by Dinesh Thakur Category: SQL Tutorials

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;
DEPTNO  DEPARTEMENT SALARY
   
10 8750
20 10875
30 9400

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

SQL> SELECT JOB, AVG (SAL) FROM EMP GROUP BY JOB;
   TOTAL JOBSJOB
  
3000 ANALYST
1037.5 CLERK
2758.333333 MANAGER
5000 PRESIDENT
1400 SALESMAN

To find out the jobs of individual departments.

SQL> SELECT JOB, COUNT (JOB) "TOTAL JOBS" FROM EMP GROUP BY DEPTNO, JOB;
TOTAL JOBS JOB
   
1 CLERK
1 MANAGER
1 PRESIDENT
2 ANALYST
2 CLERK
1 MANAGER
1 CLERK
1 MANAGER
4 SALESMAN
Related SQL




About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.



Search Content