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:

Let’s run all queries for GROUP BY clause for following Employee table.

Example# 1

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

OUTPUT:

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.

OUTPUT:

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

OUTPUT:

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

OUTPUT:

Example# 5

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

OUTPUT:

Example# 6

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

OUTPUT:

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.

OUTPUT:

Example# 8 

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

OUTPUT:

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.

OUTPUT:

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

Add a Comment

Your email address will not be published. Required fields are marked *