Mysql Query to count common number in a column

Question

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

Output

Yogesh Sharma · Accepted Answer · 2018-07-31 09:19:36Z

1

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;

answered Jul 31, 2018 at 9:19

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nijesh W Over a year ago

Thanks @yogesh-sharma, Assume that tsp has n number of names, above query works well for fixed tsp

flyingfox · Accepted Answer · 2018-07-31 09:26:47Z

1

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;

answered Jul 31, 2018 at 9:26

flyingfox

13.5k4 gold badges30 silver badges43 bronze badges

1 Comment

Nijesh W Over a year ago

Assume that tsp has n number of names, above query works well for fixed tsp

Collectives™ on Stack Overflow

Mysql Query to count common number in a column

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related