CODEWITHSUNDEEP
sqlt-sql
Tan Singh
Tan Singh

Reputation: 1

Calculate average and running total in SQL

I have this following table #temp1 with the following column names

Month RecordTotalByMonth Type Product
1 10 New Wellness
2 20 New Wellness
3 30 New Wellness
4 30 New Wellness
1 15 Average Claim Size Wellness
2 15 Average Claim Size Wellness
3 30 Average Claim Size Wellness
4 10 Average Claim Size Wellness
1 10 New Accident
2 20 New Accident
3 30 New Accident
4 30 New Accident
1 15 Average Claim Size Accident
2 15 Average Claim Size Accident
3 30 Average Claim Size Accident
4 10 Average Claim Size Accident

Now I would like to calculate AVG and running total in the new column "Total or Average". Calculate running total by months where Product = 'Wellness' or Product = 'Accident' and Type = 'New'. But calculate Average where Product = 'Wellness' or Product = 'Accident' and Type = 'Average claim size'. End result should look like this

Month Record Total By Month Type Product Total or Average
1 10 New Wellness 10
2 20 New Wellness 30
3 30 New Wellness 60
4 30 New Wellness 90
1 15 Average Claim Size Wellness 20
2 15 Average Claim Size Wellness 20
3 30 Average Claim Size Wellness 20
4 20 Average Claim Size Wellness 20
1 10 New Accident 10
2 20 New Accident 30
3 30 New Accident 60
4 30 New Accident 90
1 10 Average Claim Size Accident 15
2 10 Average Claim Size Accident 15
3 30 Average Claim Size Accident 15
4 10 Average Claim Size Accident 15

My attempt

select  Monthly                                     
        , RecordTotalByMonth
        , Product
        , Type
        , sum(RecordTotalByMonth) over (partition by Product) as [Total or Average]
INTO New_table
from #temp1 
where Type = 'New'


Insert into New_table               
SELECT    Monthly                                       
        , RecordTotalByMonth
        , Product
        , Type
        , avg(RecordTotalByMonth) over (partition by Type) as [Total or Average]
from #temp1
where Type = 'Average Claim Size'

Upvotes: 0

Views: 70

Answers (1)

Buddhi
Buddhi

Reputation: 421

Can you try this:

SELECT month,
       recordtotalbymonth,
       type,
       product,
       CASE
         WHEN ( ( product = 'Wellness'
                   OR product = 'Accident' )
                AND type = 'New' ) THEN Sum(recordtotalbymonth)
                                          OVER (
                                            partition BY product, type
                                            ORDER BY month)
         WHEN ( ( product = 'Wellness'
                   OR product = 'Accident' )
                AND type = 'Average claim size' ) THEN Avg(recordtotalbymonth)
         OVER (
           partition BY product, type
           ORDER BY month)
       END AS "Total or Average"
FROM   t1;

Upvotes: 0

Related Questions