Background
If you have written queries or worked on a project that requires database support then you must have use or atleast familiar with having clause. This is also one of the popular interview questions for beginners to test database knowledge if you ask me. Simple syntax looks like -
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
So your 1st and foremost answer is that you use having clause with "group by" clause. How and why we will come to later part in of this discussion. Having said that before we proceed make sure you know what group by clause does. Also you need to have an idea of what aggregate functions are . Eg. min(), max(), count(), sum() etc.
Understanding having clause in SQL
So far we know we use having clause with group by clause. Let's answer the question why.
Let's say you have a table employee which have basic data of an employee - id, name, department etc.
Problem statement : Now we are interested to find out how many employees are there in each department and probably see the result in sorted order so that department with maximum employees is displayed first. How would you do this? Using following query -
select department, count(*) from employee group by department order by count(*) desc;
This works fine. Now let's redefine our problem statement.
Problem statement : Let's say we now want the same thing - department and number of employees in each department sorted in descending order. However this time we have an additional constraint. We want to see only those departments that have more than 10 employees in it. You would probably try -
select department, count(*) from employee group by department where count(*) > 10 order by count(*) desc;
Problem : Does not work
Error : ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
(Above error is show for oracle database)
NOTE : An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference
00934. 00000 - "group function is not allowed here"
(Above error is show for oracle database)
NOTE : An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference
Problem is that you cannot use aggregate functions in where clause. Solution? - Having clause. This is exactly why having clause was introduced. Once you have applied the group by clause and wish to filter the data further on the results obtained you use having clause. So your correct query would be -
select department, count(*) from employee group by department having count(*) > 10 order by count(*) desc;
Aggregate functions are allowed in having clause. So lets go over the original syntax again and see how it works -
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
- First you select column_name(s) from the table that match the where clause condition
- Once result is obtained then group by clause is applied to it to get the next set of result.
- Once that is done condition is having clause is applied to further filter the result.
- Finally order by clause is applied to sort the result as required and return.
NOTE : where clause us applied to filter results before group by clause is applied where as having clause is applied after.
Other alternative can be like -
select department, count from ( select department, count(*) count from employee group by department )\ where count>10;
