CODEWITHSUNDEEP
mysqlsql
Nijesh W
Nijesh W

Reputation: 57

Mysql Query to count common number in a column

I have a main table as first image and I need to produce output as second table. Can any one please help me to do mysql query.

Main Table

enter image description here

Output

enter image description here

Upvotes: 1

Views: 63

Answers (2)

flyingfox
flyingfox

Reputation: 13506

Try with CASE WHEN

   select anumber,
           sum(case when tsp = 'aplace' then 1 else 0 end) as aplace,
           sum(case when tsp = 'bplace' then 1 else 0 end) as bplace,
           sum(case when tsp = 'cplace' then 1 else 0 end) as cplace,
    from table t
    group by anumber;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do conditional aggregation :

select anumber,
       sum(tsp = 'aplace') as aplace,
       sum(tsp = 'bplace') as bplace,
       sum(tsp = 'cplace') as cplace
from table t
group by anumber;

Upvotes: 1

Related Questions