Question: Can a Select statement have HAVING clause and no GROUP BY?
I was first asked this question about 20 years ago and I have to admit I did not provide the right answer which seemed to be very counter intuitive.
The correct answer: Yes, it can.
SELECT SUM(sal) FROM emp HAVING COUNT(*)>10
Essentially, when we don’t use GROUP BY clause we treat the entire table as a single group. As with any group, we can reference various aggregate functions in SELECT, HAVING, and ORDER BY clauses.
We can even use some analytical functions as follows:
SELECT COUNT(*), SUM(COUNT(*)) OVER() sm FROM emp HAVING COUNT(*)<100
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.