CODEWITHSUNDEEP
mysql
aLL0i
aLL0i

Reputation:

MySql getting count of each type of element in a cloumn

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??

Upvotes: 1

Views: 7922

Answers (2)

Collin Cudd
Collin Cudd

Reputation: 36

Watch out for null values:

mysql> select resourcetype,count(resourcetype) as num from resources group by resourcetype     order by num desc;
+-------------------+-------+
| resourcetype      | num   |
+-------------------+-------+
| COLLECTION        | 20036 |
| HIDDEN_RESOURCE   | 17443 |
| DEFAULT_CALENDAR  | 12528 |
| CALSCHED_INBOX    | 12528 |
| CALSCHED_OUTBOX   | 12528 |
| CAL_RESOURCE      | 12367 |
| SHARING_OUTBOX    |  2465 |
| SHARING_INBOX     |  2452 |
| CALENDAR          |     8 |
| HIDDEN_COLLECTION |     4 |
| NULL              |     0 |
+-------------------+-------+

mysql> select count(*) from resources where resourcetype is null;
+----------+
| count(*) |
+----------+
|       56 |
+----------+

Upvotes: 0

David Grant
David Grant

Reputation: 14223

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.

Upvotes: 7

Related Questions