Interview Question: Can a Select statement have HAVING clause and no GROUP BY?

Question: Can a Select statement have HAVING clause and no GROUP BY?
Level: Intermediate/Advanced.

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.

Example:

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s