Link to home
Start Free TrialLog in
Avatar of rmartes
rmartes

asked on

MySql GROUP BY COUNT, including zeros

Hey Experts,

I could never figure this out. I have a query displaying the total number of downloads per category:

SELECT cat.category_id, cat.category_name, COUNT(*) ct
FROM tbl_category cat, tbl_downloads dl
WHERE dl.category_id = cat.category_id
AND dl.deleted_flag = 'N'
GROUP BY cat.category_name
ORDER BY cat.category_name;

If the category has no downloads in it, meaning '0', it does not display. I only get the categories where downloads > 0. I want display all categories, regardless if downloads = 0:

category | count
food | 2
books | 6
software | 0

Thank you in advance.
Avatar of ralmada
ralmada
Flag of Canada image

try like this

SELECT cat.category_id, cat.category_name, COUNT(*) ct
FROM tbl_category cat
left join tbl_downloads dl on dl.category_id = cat.category_id AND dl.deleted_flag = 'N'
GROUP BY cat.category_name
ORDER BY cat.category_name;

actually

SELECT cat.category_id, cat.category_name, COUNT(dl.category_id) ct
FROM tbl_category cat
left join tbl_downloads dl on dl.category_id = cat.category_id AND dl.deleted_flag = 'N'
GROUP BY cat.category_name
ORDER BY cat.category_name;
SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rmartes
rmartes

ASKER

Beautiful guys....thank you. This always stumps me.
You are most welcome. Thank you for including me, but I am glad you recognized ralmada gave you the solution. I was just supporting. Anyway, glad I could contribute and appreciate the points. Ralmada, nice work as usual!

Best regards and happy coding,

Kevin
Thanks rmartes and thanks Kevin too. It's always a pleasure reading you. By far, one of the best experts on this site!

Regards