T-sql over clause to aggregating without groupby

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