Question for mySql experts

[stefd]

Hi, I need some help to build an SQL query (I am pretty new to this stuff...)

I have 2 tables, one with products, and one with categories. Each of the products is classified in one of the categories.

So, the tables would look like this:

Categories table
================
catId
catName

Products table
==============
prodId
prodName
catId (id of category this product falls in)

What I want is to know how many products are in each category. The result I'm looking for would look like:

catId | number of products
==========================
1 | 10
2 | 20
3 | 0
4 | 0
5 | 4

and so on...

I've tried, but I can't come up with the correct SELECT query to generate the desired result.

Is it possible to build such a query, or do I have to code a loop to go through each category explicitly?

Thanks for your help!

[jetsetter]

Try this:

$result = mysql_query("SELECT catId, count(catId) as catIdCount FROM products GROUP BY catId");

Then you will have a table that looks like:

catId | catIdCount
==== ========
1 | 10
2 | 20
3 | 0
4 | 0
5 | 4

Should do the trick.

[jetsetter]

So now I bet you're wondering "Great I got the counts, but I would really like the category name next to the count, not the ID "

Well here it is if you're interested:

$result = mysql_query("SELECT catId, count(catId) as catIdCount FROM products GROUP BY catId");

while(list($catId, $catIdCount) = mysql_fetch_row($result)) {

$result2 = mysql_query("SELECT catName FROM Categories WHERE $catId=catId");

list($catName) = mysql_fetch_row($result2);

echo "$catName $catIdCount";

}

[stefd]

Ahhh! So elegant!

It does the job perfectly, thanks!!

By the way, are there any resources you migh recommend to find answers to questions like that?

Thanks again.

[jetsetter]

http://www.phpbuilder.com/ has some nice articles on PHP and mySQL.

I just kind of assumed you were using PHP. Good luck. Feel free to ask away.