Skip to content
Advertisement

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

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:

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

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:

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement