Calculate average and running total in SQL

Question

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'

@budhi my attempt is wrong, total comes upto 180 because it's only looking at Type = 'New' — Tan Singh, Sep 30 '22 at 18:24
Does this answer your question? "[Multiple Running Totals with Group By](/q/10368308/90527)", "[Calculate a Running Total in SQL Server](/q/860966/90527)", "[Running Total (pre 2012) for more than 1 group](/q/21029973/90527)", "[Average of grouped rows in Sql Server](/q/3100921/90527)", … — outis, Sep 30 '22 at 18:26
As per the [site guidelines](/help/how-to-ask) in the [help], please [search](/help/searching) before posting. See also "[How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/90527)" — outis, Sep 30 '22 at 18:33

score 1 · Answer 1 · answered Sep 30 '22 at 18:21

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;

Calculate average and running total in SQL

1 Answers1