SQL GROUP BY Statement

The SQL GROUP BY clause is used along with the SQL aggregate functions and specifies the groups where selected rows are placed. When one or more aggregate functions are presented in the SQL SELECT column list, the SQL GROUP BY clause calculates a summary value for each group.

Syntax:

SQL> SELECT column_name, aggregate_function (column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

GROUP BY is an invaluable SQL clause for organizing and performing operations on information about several different items you want to aggregate. For instance, what if you wanted to get information about employee attendance in several different time periods to do comparisons and charting? To find out the Total salary of all the Departments.

SQL> SELECT DEPTNO, SUM (SAL) "DEPARTEMENT SALARY" FROM EMP GROUP BY DEPTNO; 

List the average salary of each job in the EMP table.

SQL> SELECT JOB, AVG (SAL) FROM EMP GROUP BY JOB; 

To find out the jobs of individual departments.

SQL> SELECT JOB, COUNT (JOB) "TOTAL JOBS" FROM EMP GROUP BY DEPTNO, JOB;