mysql multiple count and group values in a same field

Question

I was looking around but couldn't find an answer I need a query that return 2 groups of values as field names based on values in a same field

example I have a table

NAME, VALUE
name1, 2
name1, 2
name1, 3
name1, 4
name2, 2
name2, 2
name2, 3
name2, 4

now I want to count and group values 2 and 3 in one group and values 4 in another group so my result would look like this

NAME, GRP1_COUNT, GRP2_COUNT
name1, 3, 1
name2, 3, 1

I tried the JOIN and UNION without much luck any help appreciated

Answer 1

MySQL does not have a pivot function so you will have to transform the data using an aggregate function with a CASE expression. For this type of calculation you will use either COUNT or SUM:

select name,
  sum(case when value in (2,3) then 1 else 0 end) GRP1_COUNT,
  sum(case when value = 4 then 1 else 0 end) GRP2_COUNT
from yourtable
group by name

See SQL Fiddle with Demo

COUNT version:

select name,
  count(case when value in (2,3) then VALUE end) GRP1_COUNT,
  count(case when value = 4 then VALUE end) GRP2_COUNT
from yourtable
group by name

See SQL Fiddle with Demo

Answer 2

Try this

SELECT
name,
sum(case when value=2 or value=3 then 1 else 0 end),
sum(case when value=4 then 1 else 0 end)
FROM YourTable
GROUP BY name