Group by Clause in Sql server

The Group By clause is used to grouping or to priovide summary data for column returned in a SELECT statement.It Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions in SQL.

GROUP BY Clause is used with SELECT Command, and specifies the groups into which output rows are to be placed by the values of one or more columns or expressions.  And if aggregated functions are used in the SELECT statements, then GROUP BY clause computes a summary value for each group.

Grouping on Column:

On a single column or multiple columns we can group the data.

Syntax :Select <Aggregate functions> (<col1>),<col2> from <Tablename> GROUP BY <Col2>

The Group by clause can be used with or without Aggregate function.

Also we can use the one aggregate function on single column and non agrrigate function on all other columns.

For better understanding of Grouping on column create the following table on Sqlserver.

ID	CarCompanyName	CarName	Sales	Date
1	Maruthi	Maruthi-800	100	3/20/2012
2	Maruthi	WagnorDuo	200	3/20/2012
3	Tata	indica	500	3/20/2012
4	Tata	Sumo	400	2/20/2012
5	Tata	Sumo	200	1/20/2012
6	Honda	City	100	3/20/2012
7	Mahindra	Scorpio	150	3/20/2011
8	Mahindra	Scorpio	100	3/20/2012

Now If we want to retrieve the all sales with respect to carcomapny name the following query will give this result :

 select carcompanyName,SUM(sales)as sales from [Tbl_CarSales]

 group by carcompanyName

here we are using the aggrigate function to find the sales of particular car company name.

OutPut:

carcompanyName	sales
Honda	100
Mahindra	250
Maruthi	300
Tata	1100

Suppose  if we want to retrieve grouping the sales details of the carcompany name as well car name use the following Query :

 select carcompanyName,[CarName],SUM(sales)as sales from [Tbl_CarSales]

 group by carcompanyName,[CarName]

In the above Query we are using the multiple columns in grouping.

OutPut:

carcompanyName	CarName	sales
Honda	City	100
Tata	indica	500
Maruthi	Maruthi-800	100
Mahindra	Scorpio	250
Tata	Sumo	600
Maruthi	WagnorDuo	200

Grouping On Expression:

We can also write the or retrieve the records using an expression in the Group By Clause .An expression will contain the Agrigatefunctions.

Syntax :Select <Aggrigatefunctions> (<col1>),<col2> from <Tablename> GROUP BY <Expression>

Suppose If we want to retrieve the all the sales details in each year  then use the following query :

select Year(date) as year,carcompanyName,[CarName],SUM(sales)as sales from   [Tbl_CarSales]

group by carcompanyName,[CarName],Year(date)

here we are using the column names and expression (Year(date)) to group the records.

OutPut :

year	carcompanyName	CarName	sales
2012	Honda	City	100
2011	Mahindra	Scorpio	150
2012	Mahindra	Scorpio	100
2012	Maruthi	Maruthi-800	100
2012	Maruthi	WagnorDuo	200
2012	Tata	indica	500
2012	Tata	Sumo	600

Similarly try to retrieve the all the sales in each month.