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 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
