Rules for HAVING clause is not so easy as it may look like at first glance. It is an assumption that HAVING clause can contain the same grouping/aggregation as SELECT and it will work just fine because the role of HAVING is to filter whatever has left after aggregation done by GROUP BY.

The query below will work just fine

SELECT MAX(order_total)
FROM orders_direct
GROUP BY customer_id
HAVING MAX(order_total)> 50;

and we always assume that HAVING is some sort of filter that can use fields used in SELECT. True? It turns out not everything is so easy. In fact, the query below won’t work

SELECT SUM(MAX(order_total))
FROM orders_direct
GROUP BY customer_id
HAVING SUM(MAX(order_total))> 50;

And the reason for that is that nested aggregation requires GROUP BY and for SELECT it has it. But HAVING does not, therefore it won’t work. But if you change a little bit HAVING clause (see below) it will work again

SELECT SUM(MAX(order_total))
FROM orders_direct
GROUP BY customer_id
HAVING MAX(order_total)> 50;