How To use the HAVING clause in SQL query?

SQL having clause is used where you have to use some kind of aggregate as a condition.

SYNTAX:

SELECT *
FROM DEMO_TAB
GROUP BY COL
HAVING CONDITION
ORDER BY COL

EXAMPLE

This is a demo table.

Now suppose you have to find the subjects with average marks more than 80, here is how you will do it.

SELECT SUBJECT, AVG(MARKS) AS AVERAGE
FROM MARKS_DATA
GROUP BY SUBJECT
HAVING AVG(MARKS) >80

Here is what happens when you use WHERE clause instead of the HAVING clause.

SELECT SUBJECT, AVG(MARKS) AS AVERAGE
FROM MARKS_DATA
GROUP BY SUBJECT
WHERE AVG(MARKS) >80

Error occurred because we cannot use aggregates as condition in SQL query.

Related Posts