SQL- Group by Having Clause

In database systems many times you need to summarize the data stored in tables in answer to adhoc queries of the users. For this SQL Aggregate Functions can be used. But if your need to summarize the data on the basis of some group of values, then you must understand SQL- Group by Having Clauses

What is SQL GROUP BY HAVING?

To understand SQL Group By Having first understand where it can be applied. Here are a few situations where you might need to summarize data for groups

• Show total salary of employees in different departments.
• List total salary paid to different jobs in the orgnaization.
• Display average marks scored by students in different subjects.
• Find out the maximum score of students in different courses.
• Display average sales done at different locations of an organizations.
• Show the total earnings of an organization month wise
• List total earnings of an organization month wise but when the order quantities are more than 700

You might have faced similar queries to be answered for your organizational needs. For such queries you will need the GROUP BY HAVING clause to be added in the SELECT Statement.

Syntax of SELECT statement with SQL GROUP BY HAVING Clauses

SELECT column-name(s), group function(s)

FROM table-name

[WHERE condition(s)]

[GROUP BY column-name]

[HAVING Group-condition]

In this statement you have to write column names and group functions  to aggregate values after SELECT keyword. The WHERE clause is used to filter those rows which you want to include in summarized data. It means that WHERE clause filters the rows before applying the aggregate function. GROUP BY clause includes the column names for which you want to group the data showing the aggregate values. HAVING clause filters the final data after grouping and aggregating. It applies condition on aggregate functions’ output.

Points to ponder:

• The non aggregate column names in the SELECT clause must be included in the GROUP BY clause.
• The HAVING clause can include aggregate function other than those used in the SELECT clause.

Example

SELECT Job_Desc, SUM(Salary)

FROM Emp_Table

WHERE Deptt_id IN (‘sales’,’HR’,’Accounts’,’Training’)

GROUP BY Job_Desc

HAVING SUM(Salary)>10000

The above query will display total salaries for different Job descriptions from Employees table filtering data for sales, HR, Accounts and Training departments. The final results will display only those job descriptions whose total salary is greater than 10000.

Example Queries for Group By Having

The orders table is created with the data as shown below

Example 1

Find the total dollars earned month wise.

Query

select month, sum(dollars)
from orders
group by month

Output

Example 2

Display the dollars earned month wise when the total dollars earned are greater than 700.

Query

select month, sum(dollars)
from orders
group by month
having sum(dollars)>750

Output

Example 3

Display the dollars earned month wise when the total dollars earned are greater than 700 but only for those orders where quantity ordered is greater than 500 units

Query

select month, sum(dollars)
from orders
where qty>500
group by month
having sum(dollars)>750

Output

Example 4

Display the total dollars earned month wise when the average quantity ordered is greater than 800 but only for those orders where quantity ordered is greater than 500 units

Query

select month, sum(dollars)
from orders
where qty>500
group by month
having avg(qty)>800

Output

These queries are executed in Oracle Live. If you don’t have Oracle Desktop version, you can try here by signing up.

admin