SQL Count Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
r_barlow
Forum Newbie
Posts: 17
Joined: Mon May 29, 2006 3:13 pm

SQL Count Problem

Post by r_barlow »

When you are using a COUNT with a GROUP BY, how do you make it so this returns 0 if what you're grouping by doesn't have anything in the condition.

For example:

SELECT p.categoryid, COUNT(p.categoryid) FROM products p, product_categories pc WHERE p.categoryid = pc.categoryid GROUP BY p.categoryid;

Right now this returns the following if no products have a category of 1.

categoryid count
2 4
3 10
4 9

I need it to return the category 1 with 0 count.

Sorry if I've explained this horribly... its late in the day!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

GROUP BY groups by what is in the table. If there are no entries for it, it won't group it. I am not sure you can get what you are after directly.
Post Reply