Using Over() to handle totals

OVER() is fantastic for Business Intelligence and possibly under-rated, useful when you want to create a total for a particular grouping in your query but still want to see the granular transactions. The following example highlights how to sum the quantity sold for the total sale but still show the item level saleline quantity sold values.

CREATE TABLE #tmpTable

(

SaleId int,
SaleLineId int,
SaleDateTime datetime,
SoldQty int
) 

INSERT #tmpTable
VALUES
(1,100,'2012-05-01 08:00:00.000', 2)
GO
INSERT #tmpTable
VALUES
(1,200,'2012-05-01 08:00:00.000', 5)
GO
INSERT #tmpTable
VALUES
(1,300,'2012-05-01 08:00:00.000', 1)
GO
INSERT #tmpTable
VALUES
(2,400,'2012-05-01 09:00:00.000', 9)
GO
INSERT #tmpTable
VALUES
(2,500,'2012-05-01 09:00:00.000', 7)
GO


select SaleId,  SaleLineId, SaleDateTime, SoldQty, 
      sum(SoldQty) OVER (Partition by SaleId) as SaleTotal
from #tmpTable

drop table #tmpTable

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s