I have a table in SQL that has two columns of data, first is a week number, second is a value. There can be multiple records for the same week. Here’s some sample data (I added some SQL for this data at the end):
id | WeekNo | MyAmount |
---|---|---|
1 | 1 | 5200.00 |
2 | 1 | 180.00 |
3 | 1 | 100.00 |
4 | 2 | 100.00 |
5 | 2 | 50.00 |
6 | 3 | 100.00 |
7 | 3 | 100.00 |
8 | 4 | 100.00 |
9 | 4 | 900.00 |
10 | 4 | 1000.00 |
I have a query that sums these values by week, and adds another column that contains the cumulative value of all weeks. This works OK
with data as ( SELECT WeekNo, SUM(MyAmount) As TheAmount FROM #tTryIt GROUP BY WeekNo ) SELECT WeekNo, TheAmount, SUM(TheAmount) over (order by WeekNo asc rows between unbounded preceding and current row) as Cumulative FROM data
WeekNo | TheAmount | Cumulative |
---|---|---|
1 | 5480.00 | 5480.00 |
2 | 150.00 | 5630.00 |
3 | 200.00 | 5830.00 |
4 | 2000.00 | 7830.00 |
Now what I am wanting to do is to calculate each week’s value percentage against the cumulative, but to also include the previous week (eg if in week 2 the week 2 value is 25% then the week 1 value must be 75% of the cumulative total), however so far I have only been able to calculate the percentage for the current week, not the previous week(s).
Here is my current query and result:
with data as ( SELECT WeekNo, SUM(MyAmount) As TheAmount FROM #tTryIt GROUP BY WeekNo ) SELECT WeekNo, TheAmount, SUM(TheAmount) over (order by WeekNo asc rows between unbounded preceding and current row) as Cumulative, (TheAmount/SUM(TheAmount) over (order by WeekNo asc rows between unbounded preceding and current row))*100 as WeekPct FROM data
WeekNo | TheAmount | Cumulative | WeekPct |
---|---|---|---|
1 | 5480.00 | 5480.00 | 100.000000 |
2 | 150.00 | 5630.00 | 2.664200 |
3 | 200.00 | 5830.00 | 3.430500 |
4 | 2000.00 | 7830.00 | 25.542700 |
But what I really need to get is something like this (where WeekNo
is the actual week for the cumulative total, and the PctWeek
is whichever week we’re calulating the percentage for):
WeekNo | PctWeek | TheAmount | Cumulative | WeekPct |
---|---|---|---|---|
1 | 1 | 5480.00 | 5480.00 | 100.0000 |
2 | 1 | 5480.00 | 5630.00 | 97.3358 |
2 | 2 | 150.00 | 5630.00 | 2.6642 |
3 | 1 | 5480.00 | 5830.00 | 93.9966 |
3 | 2 | 150.00 | 5830.00 | 2.5729 |
3 | 3 | 200.00 | 5830.00 | 3.4305 |
4 | 1 | 5480.00 | 7830.00 | 69.9872 |
4 | 2 | 150.00 | 7830.00 | 1.9157 |
4 | 3 | 200.00 | 7830.00 | 2.5544 |
4 | 4 | 2000.00 | 7830.00 | 25.5427 |
How can I actually go about getting my query to output the data I need?
Sample SQL data below
CREATE TABLE #tTryIt ( id INT PRIMARY KEY, WeekNo INT, MyAmount DECIMAL(18,2) ) INSERT INTO #tTryIt VALUES (1, 1, 5200), (2, 1, 180), (3, 1, 100), (4, 2, 100), (5, 2, 50), (6, 3, 100), (7, 3, 100), (8, 4, 100), (9, 4, 900), (10, 4, 1000);
Advertisement
Answer
Basically, you can take your query and then join it again with all the weeks. This gives you the first three columns. You can fill in the last two using window functions:
with data as ( SELECT WeekNo, SUM(MyAmount) As TheAmount FROM TryIt GROUP BY WeekNo ) select w.weekno, d.weekno as pctweek, d.theamount, sum(d.theamount) over (partition by w.weekno) as cumulative, d.theamount * 100.0 / sum(d.theamount) over (partition by w.weekno) as pct from data d join (select distinct weekno from TryIt) w on d.weekno <= w.weekno order by 1, 2;
Here is a db<>fiddle.