Search code examples
mysqlsqlrdbms

How to Group and display count of each type of data?

Suppose I have a column which has three types of data like A, B, C. I want to group and count the number of each type. For example if A is 3 times in column , B is 2 times and C is 1 time. It should display as:

A    B     C
3    2     1

I would appreciate your help. Thankyou.

Solution

  • If you want the data in one row, you can use conditional aggregation:

    select sum(col = 'a') as A, sum(col = 'b') as b, sum(col = 'c') as c
from t;

    You can try the more explicit:

    select sum(case when col = 'a' then 1 else 0 end) as A,
       sum(case when col = 'b' then 1 else 0 end) as b,
       sum(case when col = 'c' then 1 else 0 end) as c
from t;