03 Mar

HAVING Without GROUP BY

Yes, it’s possible.

This is something that not a lot of people know about, but, it turns out that you can, in fact, use HAVING without a GROUP BY.

From the official documention,

“When GROUP BY is not used, HAVING behaves like a WHERE clause.”

http://msdn.microsoft.com/en-us/library/ms180199.aspx

And, that is it for the documentation. Essentially from what I can tell it only works in situations where you have aggregate data. For example.

SELECT SUM(spending) as totSpending
FROM militaryspending
HAVING SUM(spending) > 200000;

totSpending
1699154.3

And,

SELECT SUM(spending) as totSpending
FROM militaryspending
HAVING SUM(spending) < 200000;

totSpending

If I were to change the SELECT to region, SUM(spending) the query fails.

SELECT region, SUM(spending) as totSpending
FROM militaryspending
HAVING SUM(spending) < 200000;

Msg 8120, Level 16, State 1, Line 5
Column ‘militaryspending.region’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Finally, you can have multiple aggregates in the function.

SELECT SUM(spending) AS totSpending, AVG(spending) AS AvgSpending
FROM militaryspending
HAVING SUM(spending) > 200000;

totSpending	AvgSpending
1699154.3	13485.3515873016

This also works in MySQL.

mysql> SELECT SUM(spending)
    -> FROM militaryspending
    -> HAVING SUM(Spending) > 100000;
+---------------+
| SUM(spending) |
+---------------+
|     1699254.3 |
+---------------+
1 row in set (0.03 sec)

Really, while the official manual says it acts like a WHERE that’s really just another way of saying it acts like a HAVING clause with a single row in the result set.

Enjoy.

One thought on “HAVING Without GROUP BY

Leave a Reply

Your email address will not be published. Required fields are marked *