Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


SQL HAVING Clause Statement


SQL HAVING Clause statement used with GROUP BY clause for filtering the GROUP BY clause result set data allow only group of result whose HAVING clause condition TRUE.

SQL HAVING clause use with GROUP BY clause. without GROUP BY clause you can't use HAVING clause.


SQL HAVING Clause Syntax

Considering following syntax that help you to understanding HAVING clause,

SELECT 
	column_name1, column_name2, aggregate_function(column_name), ....
	FROM table_name
    [ WHERE condition ]
    GROUP BY column_name1, ...
    HAVING condition;

SQL HAVING Clause Example Statement

We have following employee_hour table that store weekday hours for each employee:

SQL> SELECT * FROM employee_hour;

NAME                 DAY             HOURS
-------------------- ---------- ----------
Opal Kole            Monday              8
Max Miller           Monday              8
Beccaa Moss          Monday              8
Paul Singh           Monday              9
Opal Kole            Tuesday             9
Max Miller           Tuesday             6
Beccaa Moss          Tuesday            10
Paul Singh           Tuesday             8
Opal Kole            Wednesday           7
Max Miller           Wednesday           9
Beccaa Moss          Wednesday          11
Paul Singh           Wednesday          12

12 rows selected.

Run it...   »

Example Statement :

SQL> SELECT name,SUM(hours) 
	FROM employee_hour 
	GROUP BY name 
	HAVING SUM(hours) > 25;

NAME                 SUM(HOURS)
-------------------- ----------
Beccaa Moss                  29
Paul Singh                   29

Run it...   »

GROUP By clause apply on name column with use SUM aggregate function to summation the total working hours. Also with HAVING clause condition allow only sum of hours grater than 25.


Example Statement :

SQL> SELECT name,AVG(hours) 
	FROM employee_hour 
	GROUP BY name 
	HAVING AVG(hours) > 8;

NAME                 AVG(HOURS)
-------------------- ----------
Beccaa Moss          9.66666667
Paul Singh           9.66666667

Run it...   »

GROUP By clause apply on name column with use AVG aggregate function to average the total working hours divide by number of day. Also with HAVING clause condition allow only avg of hours grater than 8.