MySQL count in same table including zero values

In mysql table i want to get the records including those which having 0 values

SELECT user_id, COUNT(user_id) AS mycount FROM admin_queuing WHERE job_status=‘Pending’ GROUP BY user_id ORDER BY queue_date, queue_time ASC

It returns me all those having counter more than 0 but i want to include those having counter 0 as well. Example below:

User_id, mycount
A 10
B 5
C 3
D 12
E 0
F 0

Any help would be much appreciated

Assuming you’ve got a table that lists user_id’s somewhere, you will join this query to that table using a LEFT JOIN.

The reason your query doesnt return E or F is that… it has no possible way of knowing E and F exist, because there are no instances of E or F in the admin_queuing table.

Perfect excellent many thanks for your help :slight_smile:

LEFT JOIN as @m_hutley said

also, be aware that in a GROUP BY query you can’t ORDER BY columns that aren’t in the SELECT list – you’ll get indeterminate results

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.