Geek asks and answers

Mysql multiple count and group values ​​in one field

I looked around but could not find an answer I need a query that returns 2 groups of values ​​as field names based on values ​​in one field

For 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 will look like this:

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

I tried JOINand UNIONwithout much luck any help appreciated

+5
source share
2 answers

MySQL , CASE. COUNT, 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

. SQL Fiddle with Demo

COUNT :

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

SQL

+14

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
+5

More articles:


All Articles