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

Search for "mysql rows to columns" and "mysql pivot"

Kermit
– Kermit

2013-03-21 20:08:00 +00:00
Commented Mar 21, 2013 at 20:08 — Kermit
– Kermit, Commented Mar 21, 2013 at 20:08

Taryn · Accepted Answer · 2013-03-21 20:08:29Z

15

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

answered Mar 21, 2013 at 20:08

Taryn

249k57 gold badges375 silver badges409 bronze badges

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

2 Comments

Deckard Over a year ago

Is there any neater way? Not including the hardcorded value.

Taryn Over a year ago

@Deckard yes you can use a prepared statement to execute dynamic sql

Thanos Darkadakis · Accepted Answer · 2013-03-21 20:08:18Z

5

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

answered Mar 21, 2013 at 20:08

Thanos Darkadakis

1,7292 gold badges18 silver badges31 bronze badges

4 Comments

Lamak Over a year ago

@GordonLinoff Not so hard to do when posting a code only answer. Not that this answer isn't upvote worthy, but the comparison is just not fair

Gordon Linoff Over a year ago

@Lamak . . . when I wrote that comment, both answers had just the code. The other answer has been modified and much improved with explanation (as is usually the case for bluefeet).

Thanos Darkadakis Over a year ago

i'm just happy that my first ever answer on this site (or any site like this) has (had) the exact same code with an expert!

Deckard Over a year ago

Is there any neater way? Not including the hardcorded value.

Collectives™ on Stack Overflow

mysql multiple count and group values in a same field

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related