15 November 2012

SQL GROUP BY Clause

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.

For Example: If you want to know the total amount of salary spent on each department, the query would be:
SELECT dept, SUM (salary) 
FROM employee 
GROUP BY dept; 
The output would be like:
deptsalary
------------------------------
Electrical25000
Electronics55000
Aeronautics35000
InfoTech30000
NOTE: The group by clause should contain all the columns in the select list expect those used along with the group functions.
SELECT location, dept, SUM (salary) 
FROM employee 
GROUP BY location, dept;
The output would be like:
locationdeptsalary
---------------------------------------
BangaloreElectrical25000
BangaloreElectronics55000
MysoreAeronautics35000
MangaloreInfoTech30000