selecting from multiple tables with counter

I have two tables one holds a category with column catID,catName and the other has the catID as a foreign key, now i want to select all the total items on the second table based on their catID. E.g. What is th开发者_C百科e total number of individual element if their category is 1,2,3,4 etc. Pls code hints will help thanks.

SELECT
   c.cat_id
   ,count(*) as occurence
FROM category c
INNER JOIN table2 t ON (c.cat_id = t.cat_id)
GROUP BY c.cat_id

If you want the categories with occurence = 0 then do:

SELECT
   c.cat_id
   ,count(t.cat_id) as occurence
FROM category c
LEFT JOIN table2 t ON (c.cat_id = t.cat_id)
GROUP BY c.cat_id

Links:
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html
http://www.1keydata.com/sql/sqlgroupby.html

 SELECT catName, COUNT(table2.catId) FROM table1,table2   
    WHERE table1.catId=table2.catId  
GROUP BY catName