SQL GROUP BY clause

Last update on April 07 2022 12:42:13 (UTC/GMT +8 hours)

GROUP BY clause

The usage of SQL GROUP BY clause is, to divide the rows in a table into smaller groups.

The GROUP BY clause is used with the SQL SELECT statement.

The grouping can happen after retrieves the rows from a table.

When some rows are retrieved from a grouped result against some condition, that is possible with HAVING clause.

The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to an individual group.

The WHERE clause is used to retrieve rows based on a certain condition, but it can not be applied to grouped result.

In an SQL statement, suppose you are using GROUP BY, if required you can use HAVING instead of WHERE, after GROUP BY.

Syntax:

SELECT <column_list> 
FROM < table name >
WHERE <condition>GROUP BY <columns> 
[HAVING] <condition>;

Parameters:

Name	Description
table_name	Name of the table.
column_list	Name of the columns of the table.
columns	Name of the columns which will participate in grouping..

Pictorial Presentation of Groups of Data

Using GROUP BY with Aggregate Functions

- The power of aggregate functions is greater when combined with the GROUP BY clause.
- The GROUP BY clause is rarely used without an aggregate function.

SQL GROUP BY with COUNT() function

The following query displays number of employees work in each department.

Sample table: employees

SQL Code:

SELECT department_id "Department Code", 
COUNT(*) "No of Employees" 
FROM employees 
GROUP BY department_id;

Sample Output:

Department Code No of Employees
--------------- ---------------
            100               6
             30               6
                              1
             90               3
             20               2
             70               1
            110               2
             50              45
             80              34
             40               1
             60               5
             10               1

SQL GROUP BY with SUM() function

The following query displays total salary paid to employees work in each department.

Sample table: employees

SQL Code:

SELECT department_id, SUM(salary) 
FROM  employees 
GROUP BY  department_id;

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

DEPARTMENT_ID SUM(SALARY)
------------- -----------
          100       51608
           30       24900
                     7000
           90       58000
           20       19000
           70       10000
          110       20308
           50      156400
           80      304500
           40        6500
           60       28800
           10        4400

SQL GROUP BY with COUNT() and SUM() function

The following query displays number of employees, total salary paid to employees work in each department.

Sample table: employees

SQL Code:

SELECT department_id "Department Code", 
COUNT(*) "No of Employees", 
SUM(salary) "Total Salary" 
FROM  employees 
GROUP BY  department_id;

Sample Output:

Department Code No of Employees Total Salary
--------------- --------------- ------------
            100               6        51608
             30               6        24900
                              1         7000
             90               3        58000
             20               2        19000
             70               1        10000
            110               2        20308
             50              45       156400
             80              34       304500
             40               1         6500
             60               5        28800
             10               1         4400

SQL GROUP BY on more than one columns

The following query displays the department code, job id, total salary paid to employees group by department_id, job_id.

Sample table: employees

SQL Code:

SELECT department_id "Department Code", job_id, 
SUM(salary) "Total Salary" 
FROM  employees 
GROUP BY  department_id,job_id;

Sample Output:

Department Code JOB_ID     Total Salary
--------------- ---------- ------------
            110 AC_ACCOUNT         8300
             90 AD_VP             34000
             50 ST_CLERK          55700
             80 SA_REP           243500
             50 ST_MAN            36400
             80 SA_MAN            61000
            110 AC_MGR            12008
             90 AD_PRES           24000
             60 IT_PROG           28800
            100 FI_MGR            12008
             30 PU_CLERK          13900
             50 SH_CLERK          64300
             20 MK_MAN            13000
            100 FI_ACCOUNT        39600
                SA_REP             7000
             70 PR_REP            10000
             30 PU_MAN            11000
             10 AD_ASST            4400
             20 MK_REP             6000
             40 HR_REP             6500

SQL GROUP BY with WHERE clause

The following query displays the department code, total salary paid to employees group by department_id and manager_id=103.

Sample table: employees

SQL Code:

SELECT department_id "Department Code", 
SUM(salary) "Total Salary" 
FROM  employees 
WHERE MANAGER_ID = 103 
GROUP BY  department_id;

Sample Output:

Department Code Total Salary
--------------- ------------
             60        19800

SQL GROUP BY with HAVING clause

The following query displays the department id, number of employees of those groups that have more than 2 employees:

Sample table: employees

SQL Code:

SELECT department_id, count(*) "No. of Employee" 
FROM employees 
GROUP BY  department_id 
HAVING count(*)>2;

Sample Output:

DEPARTMENT_ID No. of Employee
------------- ---------------
          100               6
           30               6
           90               3
           50              45
           80              34
           60               5

Practice SQL Exercises