MySQL get day/month table/matrix?

Question

With a simple table of dates and values:

+------------+-------+
| date       | count |
+------------+-------+
| 2015-01-01 | 5     |
| 2015-01-02 | 2     |
| 2015-07-02 | 20    |

How can I get a matrix of all months as columns, and days as rows, with the values, with a single MySQL query possibly using subselects and joins?

+-----+----------+----------+---------
| Day | January  | February | March...
+-----+----------+----------+---------
| 1   | 5        | 8        | 12
| 2   | 2        | 9        | 5
| 3   | 5        | 12       | 6

Is this possible, without using PHP or any other scripting tool?

This will be used to visualize trends and peaks for days of months.

@Shadow That's not a duplicate. This question we know quite well how many months there are in a year, well in advance of the query being written. — lc., Jan 11 '17 at 06:43
@lc. The answers to the duplicate question demonstrates both static and dynamic pivoting. Pls read the duplicate question first next time. — Shadow, Jan 11 '17 at 06:44
@Shadow The question does not duplicate this one, specifically in the wording "The problem is that I cannot tell how many products I will have so the column number needs to change dynamically depending on the rows in the products table". The answer does, however, indeed also answer this question, but this happens to be a first step to a bigger answer and you would have to know to pull only that specific piece out. — lc., Jan 11 '17 at 06:50
@lc. The answer specifically tells you which is the static and which is the dynamic part of the solution. However, if you insist, here is another one: http://stackoverflow.com/questions/7674786/mysql-pivot-table Here the question does not say that the number of columns is unknown. Pivot table questions in mysql are asked on a daily basis. We should not really answer exactly the same question over and over again. — Shadow, Jan 11 '17 at 06:57
@Shadow You should ask this question on Meta I believe. The question of where we draw the line for duplicates on writing SQL queries needs to be clearly defined, because as we see here we could argue both ways. — Tim Biegeleisen, Jan 11 '17 at 07:07
I accepted one of the two similar answers. I didn't know to search for pivot table either, so maybe this will help someone else. Thanks a lot! — Niclas, Jan 11 '17 at 08:45
@Niclas I do not think that you have searched at all. There are dozens of questions around pivot tables with lots of different wording on SO already. Neither the question, nor the answers have anything to add to them. — Shadow, Jan 11 '17 at 09:27
I did search, but failed to find anything, wouldn't have posted otherwise, sorry to have offended anyone with the duplicate question. — Niclas, Jan 11 '17 at 10:23

lc. · Accepted Answer · 2017-01-11T06:39:13.227

3

What you essentially have is a pivot, and you can accomplish this with a conditional sum (i.e. a SUM with a CASE in it), grouping by the day number to get the individual rows:

select DAY(date) as Day,
       sum(case when MONTH(date) = 1 then count else 0 end) as January,
       sum(case when MONTH(date) = 2 then count else 0 end) as February,
       --...
       sum(case when MONTH(date) = 12 then count else 0 end) as December
from myTable
group by DAY(date)

edited Jan 11 '17 at 06:39

answered Jan 11 '17 at 06:37

lc.

113,939
20
158
187

Here...have an upvote, though note that your solution would aggregate all years (since using `SUM`). – Tim Biegeleisen Jan 11 '17 at 06:43
@TimBiegeleisen Same for you, and of course your answer assumes there should only be one row per day. So we're probably both right and both wrong at the same time. – lc. Jan 11 '17 at 06:52
Well I'm assuming that the `count` is already some aggregate for a particular day (and year). But I could be wrong. – Tim Biegeleisen Jan 11 '17 at 06:54
@TimBiegeleisen That's probably a fair assumption, and what little sample result data we have does back your theory up. – lc. Jan 11 '17 at 06:56
This is of course a very simplified data set, but in this case count could be number of sales in an online store, based on a date. I'm not interested in the year, since I want to get average on what days and months are popular in average, and I could also add a WHERE to filter on a specific year. – Niclas Jan 11 '17 at 08:29
@shmosel: When using `SUM(MONTH(date) = 1)` instead of `sum(case when MONTH(date) = 1 then count else 0 end)` , I only get 1 if there are any results, and 0 if there are none. Another shorter version I found that works is `SUM(IF (MONTH(DATE) = 1,COUNT,0))` – Niclas Jan 11 '17 at 08:37
@Niclas My bad, I misread. – shmosel Jan 11 '17 at 08:40

score 0 · Answer 2 · answered Jan 11 '17 at 06:37

If you just want a matrix for 2015, you can do a pivot query:

SELECT DATE_FORMAT(date, '%d') AS Day,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 1  THEN count END) AS January,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 2  THEN count END) AS February,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 3  THEN count END) AS March,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 4  THEN count END) AS April,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 5  THEN count END) AS May,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 6  THEN count END) AS June,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 7  THEN count END) AS July,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 8  THEN count END) AS August,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 9  THEN count END) AS September,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 10 THEN count END) AS October,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 11 THEN count END) AS November,
       MAX(CASE WHEN DATE_FORMAT(date, '%m') = 12 THEN count END) AS December
FROM yourTable
WHERE DATE_FORMAT(date, '%Y') = '2015'
GROUP BY DATE_FORMAT(col, '%d')

MySQL get day/month table/matrix?

2 Answers2