Running Total & Avg or Cumulative Sum & Avg in SQL

   Part - 8

Click here for you tube answer

DROP TABLE IF EXISTS [dbo].[tblEmployee]

CREATE TABLE [dbo].[tblEmployee](

[Empid] [int] NOT NULL,

[EmpName] [varchar](50) NOT NULL,

[Salary_Date] [DATE] NOT NULL,

[Salary] [int] NULL -- check with one NULL value

) 

GO

-- Inserting data in to tblEmployee table

INSERT INTO [dbo].[tblEmployee]

           ([Empid],[EmpName],[Salary_Date],[Salary])

     VALUES

           (1,'smith','2020-03-01',8000) ,(1,'smith','2020-04-01',8050)  ,(1,'smith','2020-05-01',8100)

  ,(1,'smith','2020-06-01',8150) ,(1,'smith','2020-07-01',8150)  ,(2,'Allen','2020-05-01',7000)

  ,(2,'Allen','2020-06-01',9000) ,(2,'Allen','2020-07-01',9000)  ,(3,'Peter','2020-07-01',8000)

GO

SELECT * FROM [dbo].[tblEmployee]

--> Syntax for calculting Running Total:

SELECT *, SUM([Column_X]) OVER (PARTITION BY <Column_Y> ORDER BY <Column_Z>) AS RunningTotal

FROM [dbo].[tblTable]

For below examples we use tblEmployee table

Q1. Calculate the Running Total & Avg of Salary from the tblEmployee table?

Output:

SELECT *, SUM([Salary]) OVER (ORDER BY [Empid],[Salary_Date]) AS RunningTotal

        , AVG([Salary]) OVER (ORDER BY [Empid],[Salary_Date]) AS RunningAvg

FROM [dbo].[tblEmployee]

Solution: 

In this case entire data will be treated as Single partition as we are not using the partition clause, ordering the data by Empid and salary date then the SUM & AVG will be calculated

Q2. Calculate the Running Total & Avg of Salary from the

 tblEmployee table by Employee?

SELECT *, SUM([Salary]) OVER (PARTITION BY [Empid] ORDER BY [Empid],[Salary_Date]) AS RunningTotal_ByEmpId

        , AVG([Salary]) OVER (PARTITION BY [Empid] ORDER BY [Empid],[Salary_Date]) AS RunningAvg_ByEmpId

FROM [dbo].[tblEmployee]

Solution: In this case the data will be partitioned by Empid, ordering the data by Empid and salary date then the SUM & AVG will be calculated by Employee

Its Important that the data is Sorted with unique combination to calculate the running total or average

This can the data is partitioned by Empid and ordered by Empid, here the sorting on Empid is not unique. We have 5 records of Empid 1, 3 records of Empid of 2 .

This would result is Duplicate values for running total.

SELECT *, SUM([Salary]) OVER (PARTITION BY [Empid] ORDER BY [Empid]) AS RunningTotal

    , AVG([Salary]) OVER (PARTITION BY [Empid] ORDER BY [Empid]) AS RunningTotal

FROM [dbo].[tblEmployee]

Q3. Show the value of sum of Salary & Avg of Salary of 

tblEmployee table  as separate columns?