Calculating percentages in SQL query

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);

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.

Advertisement

Answer