Calculate Sum, Average, Moving Average and Cumulative Totals without using GROUP BY.

Problem: How to do Sum, Average and percentage on a table without using group by.

Solution: OVER Clause.

A good advantage of OVER clause vs Group BY is you can select columns which are not inside the group by and also allows you to play with several window functions.

Determines the partitioning and ordering of a row set before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

Example:

In this article I am going to show you can sum, average, total, moving averages, cumulative totals without using a group by.

First we have to learn few terms before jumping into the demo.

PARTITION BY – Allows you to divide the table into small groups based on criteria specified.

If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

ORDER BY – Orders the partition based on criteria specified either ascending or descending if nothing is specified it sorts by ascending order as a default.

Either of these key words should be present when using OVER() clause.

ROW_NUMBER() – generates Incremental numbers based on the partition by or order by criteria.

SUM() – It is an aggregate system function, Calculates the sum of a result set.

AVG() – It is an aggregate system function, Calculates the Average of a result set.

Let’s create a table to do the fun part, for this example I am considering orders placed on different products.

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL

    DROP TABLE #Orders

                GO

create table #Orders

(

OrderId UNIQUEIDENTIFIER DEFAULT NewSequentialID(),

ProductName varchar(50),

Quantity int,

OrderDate DATETIME2(7)

)

GO

--Insert Some random orders.

INSERT INTO #Orders(ProductName,Quantity,OrderDate)

SELECT 'ProductA', 10027,DATEADD(dd,10,getdate())

UNION

SELECT 'ProductB',5186,DATEADD(dd,120,getdate())

UNION

SELECT 'ProductA' , 1106,DATEADD(dd,1,getdate())

UNION

SELECT 'ProductB', 8242,DATEADD(dd,-10,getdate())

UNION

SELECT 'ProductA', 8060,DATEADD(dd,110,getdate())

UNION

SELECT 'ProductB', 7565,DATEADD(dd,112,getdate())

UNION

SELECT 'ProductA', 324,DATEADD(dd,190,getdate())

--here you go we can select the column’s which are not in group by as well and do the same calculations.

SELECT ROW_NUMBER() OVER (Order by O.Orderdate asc) as Row_num,

                                                                O.ProductName,

                                                                O.OrderId,

                                                                CAST(O.OrderDate as date) as OrderDate,

                                                                O.Quantity,

                                                                CONVERT(varchar(20),SUM(Quantity) OVER (PARTITION BY (SELECT 1) ORDER BY O.Orderdate),1) AS CumulativeTotal,

                                                                CONVERT(varchar(20),AVG(Quantity) OVER (PARTITION BY (SELECT 1) ORDER BY O.Orderdate),1) AS MovingAverage,

                                                                SUM(O.Quantity)  OVER (PARTITION BY O.ProductName )             as Product_count,

                                                                (SUM(O.Quantity) OVER (PARTITION BY (SELECT NULL)))                                as Total_Products,

                                                                AVG(O.Quantity)   OVER (PARTITION BY  O.ProductName ) as Average,

                                                                CAST(1. * Quantity / SUM(Quantity) OVER(PARTITION BY  O.ProductName) *100 AS DECIMAL(5,2))AS [Percent by ProductCategory]

FROM #Orders O

ORDER by O.orderdate asc

GO

OUTPUT: