MySQL - How can I make a query for this output?

Question

I have data like this:

+-------+--------+---------------------+
| name  | status | date                |
+-------+--------+---------------------+
| Peter | 100    | 2015-06-20 12:12:00 |
| Peter | 100    | 2015-06-20 15:12:00 |
| James | 100    | 2015-06-20 10:12:00 |
| James | 200    | 2015-06-20 14:12:00 |
| James | 100    | 2015-06-21 06:12:00 |
| James | 100    | 2015-06-21 09:12:00 |
| Peter | 200    | 2015-06-21 13:12:00 |
| Peter | 100    | 2015-06-21 14:12:00 |

And I want output like this:

+----------+-------+-------+-------+
| date     | Peter | James | Total |
+----------+-------+-------+-------+
| 20150620 |     2 |     2 |     4 |
| 20150621 |     2 |     2 |     4 |
+----------+-------+-------+-------+

I use the select statement below:

select DATE_FORMAT(date, "%Y%m%d") as date,
SUM(IF(name = "Peter", 1,0)) AS "Peter",
SUM(IF(name = "James", 1,0)) AS "James", 
SUM(IF(name != "0", 1,0)) AS "Total" 
from test group by DAYOFMONTH (date);

But what should I do if I have many name values? I can't put all names in the select state in SUM(IF name ="????").

possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Barranka, Jul 03 '15 at 18:50
You are looking for table pivoting. If you Google `mysql pivot` you will get many tutorials and related SO questions. Take a look at this: http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/ — Josep Valls, Jul 03 '15 at 18:11

score 0 · Answer 1 · answered Jul 03 '15 at 18:30

To get a resultset like that (returned from a SQL SELECT statement) statement, with a separate column for each name value, you absolutely do have to include an expression in the SELECT list for each column to be returned.

The number, types and names of the columns returned from a SELECT statement must be specified when the statement runs. That can't be altered dynamically when the statement runs.

A couple of options to consider:

If you truly need this dynamic, with any number of name values, in a single query, consider returning this as separate rows, and handling the pivot on the client side.
Use a separate query to retrieve the distinct list of name values, and use the return from that to dynamically build a second statement (like the one you are currently using.)

Thank you very much spencer7593, – On the moon Jul 04 '15 at 14:36 — On the moon, Jul 04 '15 at 14:36

score 0 · Answer 2 · edited May 23 '17 at 11:58

What you want is a pivot table.

MySQL does not have a built-in pivot table contruction utility, but you can do it by hand using prepared statements:

-- Initialize a variable to store the query string
set @sql = null;
-- Build the query string for each grouped ('name') column and store it
-- into the @sql variable
select group_concat(distinct
                    concat("sum(case when name = '", name, "' then 1 
                                else 0 
                                end) as `", name, "`"
                          )
                   separator ', ')
into @sql
from test;
-- Complete the query string and store it in the @sql variable
set @sql = concat("select date_format(`date`, '%Y%m%d') as `dt`", @sql, " from test group by date(`dt`)");
-- Create a prepared statement and execute it
prepare stmt from @sql;
execute stmt;
-- When you're done, deallocate the prepared statement
deallocate prepare stmt;

Here's a working example in SQLfiddle.

Check this question and its answers for more information.

Hope this helps you.

@Onthemoon if you find this answer useful, upvote it, or, if it solves tour problem, accept it — Barranka, Jul 04 '15 at 14:48

MySQL - How can I make a query for this output?

2 Answers2