Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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