CODEWITHSUNDEEP
mysqlsqlsumrolling-computation
thalacker
thalacker

Reputation: 2827

Rolling 12 month data Sum operation

I have seen rolling 12's on averages and counts, but not on a sum.

I have a table like so

ID | Profile_ID | EntryLocation | EntryMonth   |    Usage 
----------------------------------------------------------- 
1  |    1       |   11          |   2019-06-01 |    60.00  
2  |    1       |   12          |   2019-05-01 |    40.00  
3  |    1       |   11          |   2019-05-01 |    30.00  
4  |    2       |   11          |   2019-06-01 |   100.00 
5  |    1       |   12          |   2019-06-01 |    74.00  
6  |    2       |   12          |   2019-06-01 |   456.00 
7  |    2       |   11          |   2019-05-01 |    33.00 
8  |    1       |   11          |   2019-07-01 |   200.00 
9  |    1       |   11          |   2018-06-01 |   100.00

And what I want to do is sum up the usage all the previous months, grouping on Profile_ID,EntryLocation, and EntryMonth (which make up a clustered primary key (i.e. the combination of the three is unique).

So the result I am looking for would be the following:

ID | Profile_ID | EntryLocation | EntryMonth   |    Rolling12Usage 
-----------------------------------------------------------
1  |    1       |   11          |   2018-06-01 |   100.00  
2  |    1       |   11          |   2019-05-01 |   130.00 --> (100 + 30) 
3  |    1       |   11          |   2019-06-01 |    90.00 -->(30 + 60 (note the 100 fell off))
4  |    1       |   11          |   2019-07-01 |   290.00 -->(90 + 200)
5  |    1       |   12          |   2019-05-01 |    40.00  
6  |    1       |   12          |   2019-06-01 |   114.00 -->(40 + 74)
7  |    2       |   11          |   2019-05-01 |    33.00  
8  |    2       |   11          |   2019-06-01 |   133.00 -->(33 + 100)
9  |    2       |   12          |   2019-06-01 |   456.00

I have tried this with grouping 

select x.Profile_ID, x.EntryLocation, x.EntryMonth, sum(Usage) as [SumUsage],
       (sum(sum(Usage)) over (order by x.EntryMonth rows between 11 preceding and current row) 
       ) as Sum_12month
from my_table_with_values x
group by x.EntryMonth, x.Profile_ID, x.EntryLocation
order by x.EntryMonth

But couldn't get the ordering right. Any ideas/help?

Bonus points if you want to add the solution for doing it WITHOUT assuming a clustered primary key on those values (I bet that situation may be more/as common as mine).

Upvotes: 1

Views: 186

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I think you need a partition by:

select x.Profile_ID, x.EntryLocation, x.EntryMonth, sum(Usage) as [SumUsage],
       sum(sum(Usage)) over (partition by Profile_ID, EntryLocation
                             order by x.EntryMonth
                             rows between 11 preceding and current row
                            ) as Sum_12month
from my_table_with_values x
group by x.EntryMonth, x.Profile_ID, x.EntryLocation
order by x.EntryMonth;

If you don't have all months, you can use range instead of rows:

select x.Profile_ID, x.EntryLocation, x.EntryMonth, sum(Usage) as [SumUsage],
       sum(sum(Usage)) over (partition by Profile_ID, EntryLocation
                             order by x.EntryMonth
                             range between interval 11 month preceding and current row
                            ) as Sum_12month
from my_table_with_values x
group by x.EntryMonth, x.Profile_ID, x.EntryLocation
order by x.EntryMonth;

Upvotes: 1

Error_2646
Error_2646

Reputation: 3791

Is this what you are looking for?

SELECT x.Profile_ID, 
       x.EntryLocation,
       x.Entry_Month,
       SUM(x2.Usage)
  FROM my_table_with_values x
  LEFT
  JOIN my_table_with_values x2
    ON x.Profile_id = x2.Profile_id
   AND x.Entry_Location x2.Entry_Location
   AND x2.Entry_Month BETWEEN DATEADD(month, -11, x.entrymonth)
        And x.entrymonth 
 GROUP
    BY x.Profile_ID, 
       x.EntryLocation,
       x.Entry_Month

Upvotes: 1

Related Questions