T-sql over clause to aggregating without groupby
3.67/5 (3 votes)
A nice way to get aggregated value without group by
Whenever we are to aggregate something, we group the query on the field on which we want the aggregation.
Consider the condition suppose we have to show sales of all items with the sum of their group - we have four Item groups Food, beverage, cosmetics and others and our sale is like this:
Item Group Sales --------------------------------- Item1 Food 100 Item2 Beverage 250 Item3 Food 200 Item4 Food 300 Item5 Others 500 Item6 Beverage 100 Item7 Cosmetics 1000 Item8 Cosmetics 1000 Item9 Others 500 Item10 Food 300 Item11 Others 700 ----------------------------------and we want the result as:
Item Group Sales Total Group Sale ---------------------------------------------------- Item1 Food 100 900 Item2 Beverage 250 350 Item3 Food 200 900 Item4 Food 300 900 Item5 Others 500 1700 Item6 Beverage 100 350 Item7 Cosmetics 1000 2000 Item8 Cosmetics 1000 2000 Item9 Others 500 1700 Item10 Food 300 900 Item11 Others 700 1700 -----------------------------------We can achieve this by using over clause- First create the table-
Create table #temp ( Item Varchar(200), Group varchar(200), sales INT)
--Insert values
INSERT INTO #TEMP
select Item1,Food,100 union all
select Item2,Beverage,250 union all
select Item3,Food,200 union all
select Item4,Food,300 union all
select Item5,Others,500 union all
select Item6,Beverage,100 union all
select Item7,Cosmetics,1000 union all
select Item8,Cosmetics,1000 union all
select Item9,Others,500 union all
select Item10,Food,300 union all
select Item11,Others,700
Now to get the desired result we can write the query as-
Select Item, Group, Sales, sum(Sales) OVER (Partition by Group) as 'Total Group Sale' from #temp
means we can aggregate the values without group by and the syntax for this is very simple.
select <your column list> ,<aggregating function>(<col to aggregate>) over (partition by <column list to group>)
Hope this will help you sometime.
--Pankaj