MySQL. How can I make a request for this output?
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 to conclude like this:
+----------+-------+-------+-------+
| date | Peter | James | Total |
+----------+-------+-------+-------+
| 20150620 | 2 | 2 | 4 |
| 20150621 | 2 | 2 | 4 |
+----------+-------+-------+-------+
I am using the operator select
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 if I have many names? I cannot put all names in
select state in SUM(IF name ="????")
.
source to share
To get such a result set (returned by an SQL SELECT statement) with a separate column for each name value, you must definitely include the expression in the SELECT list for each returned column.
The number, types, and column names returned from the SELECT statement must be specified when the statement is executed. This cannot be changed dynamically when the statement is executed.
Several variants:
-
If you really need this dynamic, with any number of names, in a single request, consider returning this as separate lines and handling the anchor point on the client side.
-
Use a separate query to get the highlighted list of values,
name
and use return from it to dynamically build a second statement (like the one you are currently using.)
source to share
What you want is a pivot table.
MySQL does not have a built-in pivot table utility, but you can do it manually 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 at SQLfiddle.
Check out this question and its answers for more information.
Hope this helps you.
source to share