CODEWITHSUNDEEP
mysqlsql
GauravKatoch
GauravKatoch

Reputation: 73

Aggregate data by last n days for each row in MySQL

I have data in below format - 

Date        Amount
1/1/2000       1
2/1/2000       1    
3/1/2000       1
4/1/2000       2
5/1/2000       1
6/1/2000       1
7/1/2000       1

Here, each row represents Amount collected on that day, I want to prepare a table where each row represents amount collected on last 3 days. Thus for 3/1/2000 - it will show amount =3 ( Amount 1 on 1/1 , 1 on 2/1 and 1 on 3/1 , so 1+1+1 = 3

So, from above data, table I want is -

Date        Amount
1/1/2000       1    //1
2/1/2000       2    //1+1    
3/1/2000       3    //1+1+1
4/1/2000       4    //1+1+2
5/1/2000       4    //1+2+1
6/1/2000       4    //2+1+1
7/1/2000       3    //1+1+1

How to write a SQL query for this?

I tried this -

select date, sum(amount) as amount_sum
from SQLTABLE
where DATEDIFF(date,date)<=2 
group by date

Upvotes: 2

Views: 1256

Answers (2)

GauravKatoch
GauravKatoch

Reputation: 73

This can be done by using a sub-select.

SELECT date, 
       (SELECT sum(amount) 
       from SQLTABLE t2 
       WHERE DATEDIFF(t1.date,t2.date) IN (0,1,2)) amount_sum 
from SQLTABLE t1

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use correlated subqueries in order to get the Amount values of the two previous records:

SELECT `Date`, 
       Amount + 
       COALESCE((SELECT Amount 
                 FROM mytable AS t2
                 WHERE t2.`Date` < t1.`Date`
                 ORDER BY `Date` DESC LIMIT 1), 0) +
       COALESCE((SELECT Amount 
                 FROM mytable AS t2
                 WHERE t2.`Date` < t1.`Date`
                 ORDER BY `Date` DESC LIMIT 1, 1), 0) AS Amount
FROM mytable AS t1

The above query works even if there are gaps between consecutive records.

Edit:

If there are no gaps between consecutive records, then you can use the following query:

SELECT `Date`,       
       COALESCE((SELECT SUM(Amount)
                 FROM mytable AS t2
                 WHERE t2.date <= t1.date AND DATEDIFF(t1.date,t2.date) <= 2
                 ORDER BY `Date` DESC LIMIT 1), 0) AS Amount
FROM mytable AS t1

Upvotes: 1

Related Questions