MYSQL Count entries and group by two criteria with output in to a 2d table

Question

I have a table that looks something like this; (actual table is larger with several million rows)

Test_table

ID  Day Value
=============
1   1   4
2   1   -1
3   1   27
4   1   3
5   1   -2
6   1   -5
7   1   3
8   1   1
9   1   1
10  1   Null
11  2   1
12  2   1
13  2   2
14  2   -1
15  2   -3

I want to produce a table of these two columns with the count of the number of times each entry appears, a 2d table with the day down the rows, and the values across the top with each cell containing the count of entries in that criteria like the below;

Desired output

Day    Null -5  -3  -2  -1  1   2   3   4   27
==================================================================================
1      1     1       1   1  2       2   1   1
2                1       1  2   1

A query like;

select day, value, count(*) as count 
    from test_Table 
    group by day, value 
    Order by day asc,  value desc
;

produces the data as many rows and only 3 columns... How can I get the desired output?

multiple answers here search on pivot table mysql. but i don't think this will work if you have a lot of unique values in the column Value — Raymond Nijland, Aug 14 '13 at 22:41
is Null -5 -3 -2 -1 1 2 3 4 27 fixed? or could there be more values? if they are fixed then you could use Gordon Linoff SQL. — Raymond Nijland, Aug 14 '13 at 23:15

score 1 · Answer 1 · answered Aug 14 '13 at 22:41

You can do this with conditional aggregation:

select day,
       sum(value is NULL) as "NULL",
       sum(value = -5) as "-5",
       sum(value = -3) as "-3",
       sum(value = -2) as "-2",
       sum(value = -1) as "-1",
       sum(value = 1) as "1",
       sum(value = 2) as "2",
       sum(value = 3) as "3",
       sum(value = 4) as "4",
       sum(value = 27) as "27"
from test_Table 
group by day
Order by day asc;

Note two things. First, the column values are fixed. If you want dynamic column names, then you need to use dynamic SQL. Second, instead of blanks this will have 0 for the days with no count of a particular value.

Thanks. For days with no count of a particular value 0 is fine. The problem is it the column names could be pretty much any int... I guess I am going to have to investigate dynamic SQL... Any pointers? — user2676706, Aug 14 '13 at 22:48

spencer7593 · Answer 2 · 2013-08-14T23:07:03.523

The short answer is that it can't be done in MySQL.

The reason is that a SELECT statement has to specify the number of columns to be returned, a name and datatype for each column. And MySQL cannot dynamically generate columns to be returned for you.

The longer answer is that you would need a query of the form:

SELECT t.Day
     , SUM(IF(t.value IS NULL,1,0)) AS `Null`
     , SUM(IF(t.value = -5   ,1,0)  AS `-5`
     , SUM(IF(t.value = -3   ,1,0)  AS `-3`
     , ...
  FROM mytable t
GROUP BY t.Day

with each column specified in the SELECT list.

One trick you can use is to use another, separate query to help write that query you need. This has to be a separate step, a separate query. To get the list of values you want returned as column headers would be of the form:

SELECT IFNULL(v.value,'Null') AS val
  FROM mytable v
 GROUP BY v.value
 ORDER BY IF(v.value IS NULL,0,1), v.value

If you are doing this just in MySQL (and not an application), you can have MySQL help generate the required SQL text for you (using SQL to generate SQL)

SELECT CONCAT('     , SUM(IF(t.value',
         IFNULL(v.value,' IS NULL',CONCAT(' = ',v.value)),
         ',1,0)) AS `',v.value,'`'
       ) AS expr
  FROM mytable v
 GROUP BY v.value
 ORDER BY IF(v.value IS NULL,0,1), v.value

Then copy the string values returned from the expr column, paste those into an editor, and finish creating the SQL statement, like the example shown above.

The answer from Gordon shows the expression IF(col=12,1,0) can be abbreviated to col=12.

I always find myself typing that out the IF(conditional,valtrue,valfalse), but that's just the way my brain works. It's just easier for me to read.

Similarly the expression in the ORDER BY in my example...

ORDER BY IF(v.value IS NULL,0,1)

could be rewritten...

ORDER BY v.value IS NOT NULL

it can been done by using a very hacky way with prepared statement and group by check this http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Raymond Nijland, Aug 14 '13 at 23:09
@Raymond N: yes, that is the same approach. It's using SQL to generate a SQL statement, and then executing that SQL statement. — spencer7593, Aug 14 '13 at 23:12
@user26756706: pulling the data into Excel, and creating the pivot table there is usually what I do. — spencer7593, Aug 14 '13 at 23:14
@spencer7593: I was just thinking how easy this would be to do in excel... Was trying desperately to avoid that though. — user2676706, Aug 14 '13 at 23:17

MYSQL Count entries and group by two criteria with output in to a 2d table

2 Answers2