SQL HAVING Clause
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.
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;
Example
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.
Example
SQL> SELECT name,SUM(hours)
FROM employee_hour
GROUP BY name
HAVING SUM(hours) > 25;
NAME SUM(HOURS)
-------------------- ----------
Beccaa Moss 29
Paul Singh 29
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
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
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.