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)
[GROUP BY column-name]
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.
SELECT Job_Desc, SUM(Salary)
WHERE Deptt_id IN (‘sales’,’HR’,’Accounts’,’Training’)
GROUP BY Job_Desc
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
Find the total dollars earned month wise.
select month, sum(dollars) from orders group by month
Display the dollars earned month wise when the total dollars earned are greater than 700.
select month, sum(dollars) from orders group by month having sum(dollars)>750
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
select month, sum(dollars) from orders where qty>500 group by month having sum(dollars)>750
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
select month, sum(dollars) from orders where qty>500 group by month having avg(qty)>800
These queries are executed in Oracle Live. If you don’t have Oracle Desktop version, you can try here by signing up.