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.
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.
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;
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Regards
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;