WHERE vs HAVING - can you use HAVING without GROUP BY?

Question

I know WHERE filters rows before grouping and HAVING filters groups after. But a quiz asked whether HAVING works without GROUP BY and I said no. Answer was yes? How does that even work

Pooja Patel · Answer

Yes it works. Without GROUP BY the whole result is treated as one single group.

SELECT SUM(amount) FROM orders HAVING SUM(amount) > 1000;

returns one row if the total is above 1000, otherwise nothing.

Also helps to remember the logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. That's also why you can't use a SELECT alias inside WHERE.

Farhan Ali · Answer

and if a condition doesn't need an aggregate, put it in WHERE not HAVING. less rows to group

Ritika Malhotra · Answer

ah ok makes sense, whole table = one group

WHERE vs HAVING - can you use HAVING without GROUP BY?

3 answers

Related questions

Want the full explanation, not just an answer?