Group by clause:

-> By using this clause we can be able to show the output in groups. -> If we place agregate functions in groupby clause, we can display the output in a summarized fashion for each group. Syntax:
select column_name_used_in_group_by_list,agregatefunction(col2),agregatefunction(col3),......from tablename Group by list_of_columnsorder bylist_of_columns.
select dept,avg(salary) as 'avgsal' from employees
Select min(salary),sum(salary) from employees group by dept,year(doj)

Using more than one column with Group by:

Consider the below ORDERS table,
12008-11-12 00:00:001000Hansen
22008-10-23 00:00:001600Nilsen
32008-09-02 00:00:00700Hansen
42008-09-03 00:00:00300Hansen
52008-08-30 00:00:002000Jensen
62008-10-04 00:00:00100Nilsen
42008-09-03 00:00:00300Hansen
52008-08-30 00:00:002000Jensen
62008-10-04 00:00:00100Nilsen
SELECT customer,OrederDate,SUM(orederprice)FROM Oreders GROUP BY customer,OrederDAte having sum(orderprice)>700

Summarizing Data Using Cube,Rollup,Compute and ComputeBy:

Group by with Cube:

Consider the below given Inventory table,
By using Cube operator we can ble to display the data in a multi dimensional format. Query: Display how many blue tables,red tables,total tables,total blue items,toatal red items,total blue chairs,total red chairs,total chairs?
SELECT item,color,SUM(Quantity)AS qtysum FROM inventory GROUPBY item, color WITH CUBE


-> The cube operation generated rows for possible combinations of values from both item and color. For Example, not only does cube report all possible combinations of color values combined with the item value chair(Red,Blue, and Red+Blue). -> It also reports all possible combinations of item values combined with the color value Red (chair,table, and chair+table). -> For each value in the columns on the columns on the right in the GROUPBY clause, the ROLLUP operation does not report all possible combinations of values from the column, or columns, on the left. Foe example, ROLLUP does not report all the possible combinations of item values for each color value. Note -> You will not observe that there is no difference between cube and rollup when you use only one column in the groupby clause. -> You will see the actual difference between cube and rollup when you use multiple columns in the groupby clause along with cube and rollup.

Compute and Compute By clause

-> Compute and Compute By clauses are provided for backword compatibility. -> Instead of using compute try to use SQLserver analysis services provided with sqlserver2005. -> instead of using compute by use Rollup operator. -> By using compute clause we can produce actual result set along with another result set containing summarised data. -> Compute will produce the multiple result sets. Note You cannot apply as key word on the computed columns.By default columns names will be produced with the name as aggregate function. Ex: Consider the below given 'productsales' table
110135012001502011-12-23 00:00:00Doveshamp
225200018002002011-01-23 00:00:00Doveshamp
320130010003002010-08-13 00:00:00Doveshamp
4100550049006002008-06-02 00:00:00Doveshamp
550130011002002012-08-02 00:00:00Doveshamp
615375350252011-12-23 00:00:00Doveshamp
725337531002752011-10-07 00:00:00Doveshamp
8200300025005002010-10-29 00:00:00Doveshamp
9150330032001002011-07-29 00:00:00Doveshamp
10100350012003002011-11-15 00:00:00Doveshamp
1110550500502011-02-02 00:00:00Doveshamp
1250110010001002009-08-11 00:00:00Doveshamp
138012009502502010-12-23 00:00:00Doveshamp
Select * from productsales computesum(originalprice),sum(qty),sum(discount)


-> When we use compute by instead of compute it will produce multiple result sets. Note We need to use column name/s in the by clause which are used in the Order By clause only. And we cannot give alias names to the column names given in the compute and computeby clauses. Query:
Select * from productsales order by p.pname compute sum(s.originalprice),sum(s.qty),sum( by p.pname