SQL for QA: Tutorial#7 – GROUP BY Clause

The GROUP BY clause is used to organize identical data into groups often with the help of 5 Aggregate functions ie. COUNT, MIN, MAX, AVG, SUM.

GROUP BY clause is used with SELECT statement and it group the result by using one or more columns. So in short using this clause we can arrange the data in groups.

Syntax:

SELECT Column1, Column2
FROM xTable_Namex
WHERE [Conditions]
GROUP BY Column1, Column2
ORDER BY Column1, Column2;      // Optional but should come after GROUP BY

Letxs run all queries for GROUP BY clause for following Employee table.

ID | Name    | Department | Salary

---+---------+------------+-------

1  | Anita   | IT         | 10000

2  | Nita    | Testing    | 30000

3  | Supriya | Testing    | 60000

4  | Shilpa  | Testing    | 50000

5  | Mahesh  | IT         | 18000

6  | Priya   | HR         | 40000

7  | Priya   | Testing    | 42000

8  | Ganesh  | IT         | 30800

9  | Jamuna  | HR         | 85800

10 | Supriya | IT         | 66000

Example# 1

Suppose you want to display Department wise Employee Name, then the GROUP BY query will be as follows.

SELECT Department, Name
FROM Employee
GROUP BY Department, Name
ORDER BY Department, Name;

OUTPUT:

Department | Name

-----------+---------

HR         | Jamuna

HR         | Priya

IT         | Anita

IT         | Ganesh

IT         | Mahesh

IT         | Supriya

Testing    | Nita

Testing    | Priya

Testing    | Shilpa

Testing    | Supriya

In above result, the records are grouped as per Department names and employees under that department by using GROUP BY clause . we used ORDER BY clause with this query to sort the data in alphabetical order of dept names and its employee.

Example# 2

Suppose we want to display Department wise employee count, then the GROUP BY query will be as follows.

SELECT Department, COUNT(Name)
FROM Employee
GROUP BY Department;

OUTPUT:

Department | Count

-----------+-------

Testing    |  4

IT         |  4

HR         |  2

In above query records are grouped as per Department names using GROUP BY clause and by using COUNT function no of employees in each department is calculated.

Example# 3

For the same above query, if we only want to display the Department wise employee count with those departments first which have smallest emp count. So the query will be

SELECT Department, COUNT(Name)
FROM Employee
GROUP BY Department ORDER BY COUNT (Name) ASC;

OUTPUT:

Department | Count

-----------+-------

HR         |  2

Testing    |  4

IT         |  4

Here ORDER BY clause used with COUNT(Name) to display Employee count in ASCENDING ORDER .

Example# 4

Suppose you would like to display highest salary figure from each department. So the query will be as follows

SELECT Department, MAX(Salary)
FROM Employee
GROUP BY Department;

OUTPUT:

Department  |  Max

------------+-------

Testing     | 60000

IT          | 66000

HR          | 85800

Example# 5

Now if you would like to display lowest salary figure from each department. So the query will be as follows

SELECT Department, MIN(Salary)
FROM Employee
GROUP BY Department;

OUTPUT:

Department |  Min

-----------+-------

Testing    | 30000

IT         | 10000

HR         | 40000

Example# 6

If you would like to display AVERAGE salary figure of each department. So the query will be as follows

SELECT Department, AVG(Salary)
FROM Employee
GROUP BY Department;

OUTPUT:

Department  | Avg

------------+--------------------

Testing     | 45500.000000000000

IT          | 31200.000000000000

HR          | 62900.000000000000

Example# 7   GROUP BY with HAVING clause

By using GROUP BY clause, we can organise the data into groups but if you want to apply filter(CONDITION) on this summarised data, WHERE clause is not useful in that case.  we need to apply CONDITION  using  HAVING clause.

Suppose we want to display department wise employee count only for those department names whose employee count is more than 2, then the GROUP BY query with HAVING will be as follows.

SELECT Department, COUNT(ID)
FROM Employee
GROUP BY Department
HAVING COUNT(ID)x2;

OUTPUT:

Department | Count

-----------+-------

Testing    |  4

IT         |  4

Example# 8  

Suppose we want to display dept wise employee count with SUM of salaries for each department.

SELECT Department, COUNT(ID), SUM(Salary)
FROM Employee
GROUP BY Department;

OUTPUT:

Department | Count |  Sum

-----------+-------+--------

Testing    |  4    | 182000

IT         |  4    | 124800

HR         |  2    | 125800

Example# 9  

Suppose you want to display dept wise employee name and salary (only those employees whose salary is x 20000) then the GROUP BY query with WHERE clause will be as follows.

SELECT Department, Name, Salary
FROM Employee
WHERE Salaryx20000
GROUP BY Department, Name, Salary
ORDER BY Department;

OUTPUT:

Department | Name    | Salary

-----------+---------+-------

HR         | Jamuna  | 85800

HR         | Priya   | 40000

IT         | Ganesh  | 30800

IT         | Supriya | 66000

Testing    | Nita    | 30000

Testing    | Supriya | 60000

Testing    | Priya   | 42000

Testing    | Shilpa  | 50000

Hope you find it useful. Please let us know if any question you have.

Tutorial Index:

1. SQL For QA : Tutorial#1 – Select Query
2. SQL For QA : Tutorial#2 – Where Clause
3. SQL for QA: Tutorial#3 – INSERT INTO Statement
4. SQL for QA: Tutorial#4 – UPDATE Statement
5. SQL for QA: Tutorial#5 – DELETE Statement
6. SQL for QA: Tutorial#6 – ORDER BY Clause
7. SQL for QA: Tutorial#7 – GROUP BY Clause
8. SQL for QA: Tutorial#8 – UNION Clause
9. SQL for QA: Tutorial#9 – JOIN Clause