1

I have table with this format

id| name | types |
_________________
 1   sachin   100
 2   virat    50
 3   sachin   50
 4   sachin   50
 5   sachin   200
 6   virat    100
 7   virat    200

What I want to get as the result is now something like 

 name   | num of 50 |num of 100 |num of 200
 sachin |    2      |    1      | 1
 virat  |    1      |    1      | 1

What is the correct way to get here ?? I tried using group by. But I didn't get there

Any help?

Thanks

1000111
  • 13,169
  • 2
  • 28
  • 37
newbie
  • 128
  • 2
  • 15

2 Answers2

3

Also you can rely on MySQL boolean expression. 

SELECT
    name,
    SUM(types = 50) as num_50,
    SUM(types = 100) as num_100,
    SUM(types = 200) as num_200
FROM yourtable
GROUP BY name

Note:

Since MySQL boolean expression resolves into 0/1 so that you can use it inside SUM()

SUM(a=b) returns 1 only if a is equal to b

Working demo

More:

And if you use COUNT as mentioned in @JPG's answer then keep in mind the following subtleties of COUNT

Some subtleties regarding COUNT:

SELECT COUNT(0);   Result: 1

SELECT COUNT(-1);  Result: 1

SELECT COUNT(NULL); Result: 0

SELECT COUNT(71); Result: 1

SQL FIDDLE

1000111
  • 13,169
  • 2
  • 28
  • 37
2

If you just have these types, you can try this:

select
    name,
    count(case when types = 50 then 1 else null end) as num_50,
    count(case when types = 100 then 1 else null end) as num_100,
    count(case when types = 200 then 1 else null end) as num_200
from yourtable
group by name

Demo Here

Blank
  • 12,308
  • 1
  • 14
  • 32