• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

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.
0
rmartes
Asked:
rmartes
  • 4
  • 2
2 Solutions
 
ralmadaCommented:
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;

0
 
ralmadaCommented:
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;
0
 
Kevin CrossChief Technology OfficerCommented:
I agree with ralmada. I just popped in to say two things. First, the reason why is using the non-ansi JOIN, the condition in the WHERE creates and INNER JOIN; therefore, if there is no match to a category it will not show; therefore, you have to use a LEFT OUTER JOIN as shown and ensure that EVERY criteria on the right-most table is in the ON versus WHERE otherwise you will end up with an INNER JOIN again. Second, MySQL does not error on this, but you should NOT have cat.category_id in the SELECT if you do not have it in the GROUP BY as a general practice. Otherwise, you may see unexpected results when the two values (i.e., cat.category_id and cat.category_name) are not mutually exclusive. Again, it does not error in MySQL, but is just bad practice.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ralmadaCommented:
yes, mwvisa1 is right!

as best practice, all your non-agregated columns should be in the GROUP BY clause, if not don't include them in the SELECT part. It doesn't error on mySQL, but it does on other DBMS

So it should be

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_id, cat.category_name
ORDER BY cat.category_name;
0
 
rmartesAuthor Commented:
Beautiful guys....thank you. This always stumps me.
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
ralmadaCommented:
Thanks rmartes and thanks Kevin too. It's always a pleasure reading you. By far, one of the best experts on this site!

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now