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:
|
1 2 3 4 5 |
SELECT Column1, Column2 FROM <Table_Name> WHERE [Conditions] GROUP BY Column1, Column2 ORDER BY Column1, Column2; // Optional but should come after GROUP BY |
Let’s run all queries for GROUP BY clause for following Employee table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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.
|
1 2 3 4 |
SELECT Department, Name FROM Employee GROUP BY Department, Name ORDER BY Department, Name; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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.
|
1 2 3 |
SELECT Department, COUNT(Name) FROM Employee GROUP BY Department; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 |
SELECT Department, COUNT(Name) FROM Employee GROUP BY Department ORDER BY COUNT (Name) ASC; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 |
SELECT Department, MAX(Salary) FROM Employee GROUP BY Department; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 |
SELECT Department, MIN(Salary) FROM Employee GROUP BY Department; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 |
SELECT Department, AVG(Salary) FROM Employee GROUP BY Department; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 3 4 |
SELECT Department, COUNT(ID) FROM Employee GROUP BY Department HAVING COUNT(ID)>2; |
OUTPUT:
|
1 2 3 4 5 6 7 |
Department | Count -----------+------- Testing | 4 IT | 4 |
Example# 8
Suppose we want to display dept wise employee count with SUM of salaries for each department.
|
1 2 3 |
SELECT Department, COUNT(ID), SUM(Salary) FROM Employee GROUP BY Department; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 |
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 > 20000) then the GROUP BY query with WHERE clause will be as follows.
|
1 2 3 4 5 |
SELECT Department, Name, Salary FROM Employee WHERE Salary>20000 GROUP BY Department, Name, Salary ORDER BY Department; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
- SQL For QA : Tutorial#1 – Select Query
- SQL For QA : Tutorial#2 – Where Clause
- SQL for QA: Tutorial#3 – INSERT INTO Statement
- SQL for QA: Tutorial#4 – UPDATE Statement
- SQL for QA: Tutorial#5 – DELETE Statement
- SQL for QA: Tutorial#6 – ORDER BY Clause
- SQL for QA: Tutorial#7 – GROUP BY Clause
- SQL for QA: Tutorial#8 – UNION Clause
- SQL for QA: Tutorial#9 – JOIN Clause
