Forums Register Login

SQL Query for this?

+Pie Number of slices to send: Send
I have a table named 'user_details' and having 'status' and 'role' columns....



And Inserted some data...

I want to fetch three columns in out put ....

1st to display Status, 2nd to count users all having status like 'A' and 3rd column to count all users having status like 'A' and role like 'admin'

please help me writing this query
+Pie Number of slices to send: Send
Start by looking at SQL GROUP BY queries.
+Pie Number of slices to send: Send
I am not sure I understand your requirement but this hould give you a good starting point.


The trick to placing a subquery in the select clause is that the subquery must return a single value.
This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.

Hope this helps,
pm



+Pie Number of slices to send: Send
if you are on Oracle you could do this

+Pie Number of slices to send: Send
Thanks Patrick partly its working bt still have a strange problem....

When I write the blow query


Its working for status A but for status D its not working...
I mean it is showing same results for status 'D' also ....

like this....

+--------+---------------------+--------------+
| status | USers_with_status_A | User_A_Admin |
+--------+---------------------+--------------+
| A | 3 | 2 |
| D | 3 | 2 |
+--------+---------------------+--------------+

And my actual table entries are....

mysql> select * from user_details;
+----+--------------+----------+--------+--------+
| id | username | password | status | role |
+----+--------------+----------+--------+--------+
| 1 | Bhavesh | bhbh | A | admin |
| 2 | Ravinder | ravi | A | admin |
| 3 | Amit Sangwan | amit | A | normal |
| 4 | Dinesh | dahiya | D | normal |
| 5 | Mukesh | sod | D | admin |
+----+--------------+----------+--------+--------+

What to do now....
+Pie Number of slices to send: Send
I only supplied the last one to get you going in the right direction. I was not expecting to do everything. However, the following should help;
SELECT status,
(SELECT COUNT(status) as "Users with Status of A"
from B.user_details
WHERE status lIke "A%"
and B.id = A.id),
(SELECT COUNT(status) as "Users with Status of A and Admin"
from C.user_details
WHERE status lIke "A%"
and role like "admin%"
and C.id = A.id)
FROM A.user_details
GROUP BY status
+Pie Number of slices to send: Send
Welcome to the Ranch, Patrick!
+Pie Number of slices to send: Send
Thanks Jan (I think)

Oh, if only you were a real bartender! ;-)

pm
"Don't believe every tiny ad you see on the internet. But this one is rock solid." - George Washington
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 1043 times.
Similar Threads
SQL - Automatically reflecting the values of 2 columns in another column
SQL query not giving any result
Column count not matching the amount of columns in database
Updaing image field in Sql server
Row count for an update statement
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 01:36:32.