I have a server with MySQL 5.7.
I have two tables. First one t contains creating dates for each id. Second table t0 contains profit records day by day for each id.
I want to get columns with sums of profit for first and second 30 days for each id as well as for the first day.
SELECT t.created_at,
t.id,
sum(t1.profit) profit_1_week,
sum(t2.profit) profit_2_week,
sum(t3.profit) profit_1_day
FROM t
LEFT JOIN t0 t1 ON t.id = t.id
AND t1.inport_date BETWEEN t.created_at AND DATE_ADD(t.created_at, INTERVAL 30 DAY)
LEFT JOIN t0 t2 ON t.id = t.id
AND t2.inport_date BETWEEN DATE_ADD(t.created_at, INTERVAL 30 DAY) AND DATE_ADD(t.created_at, INTERVAL 60 DAY)
LEFT JOIN t0 t3 ON t.id = t.id
AND t3.inport_date BETWEEN t.created_at AND DATE_ADD(t.created_at, INTERVAL 1 DAY)
GROUP BY t.created_at,
t.id
ORDER BY t.created_at
This code runs but sums are wrong because sum of the first day much more then monthly. Where I’m wrong and how to fix it?
Advertisement
Answer
Your problem statement is not that clear, but based on your attempt, I suspect that you can do conditional aggregation:
select t.created_at, t.id,
sum(case when t0.inport_date >= t.created_at and t0.inport_date < t.created_at + interval 30 day then t0.profit else 0 end) profit_1,
sum(case when t0.inport_date >= t.created_at + interval 30 day and t0.inport_date < t.created_at + interval 60 day then t0.profit else 0 end) profit_2,
sum(case when t0.inport_date >= t.created_at and t0.inport_date < t.created_at + interval 1 day then t0.profit else 0 end) profit_3
from t
left join t0 on t0.id = t.id
group by t.created_at, t.id
order by t.created_at
The logic is to join just once, and then to use case expressions within the sum()s to choose which values should be taken into account.
I changed the date filtering logic to use half-open intervals rather than between, because it seems more relevant to me. You can adapt that as you wish according to your actual use case.