MySQL - How to populate weekly data in a month

Question

How to select data from following table group by weeks in a month

    Date       Project      Value   Week
+----------+--------------+-------+------+
 2018-11-07       A           2      45
 2018-11-08       B           4      45
 2018-11-09       C           3      45
 2018-11-12       B           6      46
 2018-11-13       A           5      46
 2018-11-14       C           6      46

(First week is end on sunday or week number in a month)

So my result should look like this.

 Project    1st Week 2nd Week 3rd Week 4th Week 5th Week  
+----------+--------+--------+--------+--------+--------
    A           0        2        5        0        0
    B           0        4        6        0        0 
    C           0        3        6        0        0

I try this one :

 SELECT project, value, week, date
 FROM module_progress
 WHERE
    created_at BETWEEN '2018-11-01 00:00:00' AND '2018-11-31
 AND date > DATE_SUB(NOW(), INTERVAL 1 WEEK) 23:59:59'
 GROUP BY week

Thank you

Take a look at https://stackoverflow.com/questions/7674786/mysql-pivot-table — jarlh, Nov 07 '18 at 07:10
Consider handling issues of data display in application code — Strawberry, Nov 07 '18 at 07:27

score 0 · Answer 1 · answered Nov 07 '18 at 07:32

Just use a sub query to get first week no for the month, and sum case statements for each week:

select year(date) as y, month(date) as m, project,
    sum(case when week=w0 then value else 0 end) as w1,
    sum(case when week=w0+1 then value else 0 end) as w2,
    sum(case when week=w0+2 then value else 0 end) as w3,
    sum(case when week=w0+3 then value else 0 end) as w4,
    sum(case when week=w0+4 then value else 0 end) as w5
from #date d
join (select year(date) as y, month(date) as m, min(week) as w0 from #date group by year(date), month(date))
    as d0 on d0.y=year(date) and d0.m=month(date)
group by year(date), month(date), project

MySQL - How to populate weekly data in a month

1 Answers1