MySql getting count of each type of element in a cloumn

Question

Hi,

I have 2 tables with the following structure..

category
{
  sub_cat_id - numeric
  sub_cat_name -  varchar
  main_cat_id - varchar (2 characters)
  main_cat_name -  varchar
}

products
{
  pid - autoincrement
  pname - varchar
  category (Will be either sub_cat_id or main_cat_id)
}

Products table

pid       pname       category
1         mobile      EL
2         shoes       1
3         shirt       1 
4         TV          EL
5         mp3 player  EL

I want to get the count of each category in the products table such that my query returns 2 for category 1 and 3 for category EL

Could someone please help me with this??

Answer 1

This should be the query you're after:

SELECT category, COUNT(*) FROM products GROUP BY category;

I would recommend thinking about the design of your category table, as it might cause you difficulties in the future.