WHERE filters rows before grouping, so WHERE SUM(...) > 100 fails with ERROR 1111 (HY000): Invalid use of group function. HAVING filters groups once the aggregates, and any ROLLUP rows, exist. Revenue by department and category, with subtotals:
SELECT pc.name AS department, c.name AS category, SUM(i.quantity * i.unit_price) AS revenue
FROM order_items AS i JOIN orders AS o ON o.id = i.order_id
JOIN products AS p ON p.id = i.product_id JOIN categories AS c ON c.id = p.category_id
JOIN categories AS pc ON pc.id = COALESCE(c.parent_id, c.id) WHERE o.status <> 'cancelled'
GROUP BY department, category WITH ROLLUP
HAVING GROUPING(category) = 1 OR revenue > 100;Output
+-------------+-------------+---------+ | department | category | revenue | +-------------+-------------+---------+ | Accessories | NULL | 94.96 | | Books | Databases | 162.50 | | Books | Programming | 350.70 | | Books | NULL | 513.20 | | NULL | NULL | 608.16 | +-------------+-------------+---------+ 5 rows in set (0.001 sec)
The Accessories row (94.96) failed revenue > 100, but its subtotal survived: GROUPING(category) is 1 on super-aggregate rows. Design is absent because WHERE removed its cancelled order before grouping. Keep conditions that need no aggregate in WHERE, where they can use an index.