I understand that the order or execution is as follows
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP
from this SO Answer as well as Microsoft Documentation
However, in my query below, the column total is built on the fly which is later used in having clause. This would mean that having executes AFTER select and not before because the column 'total' does not exist in orders table.
Am I interpreting it wrong or simply missing something?
Query
select customer_id,
sum(CASE
WHEN product_name = 'A' THEN 1
WHEN product_name = 'B' THEN 1
WHEN product_name = 'C' THEN -1
ELSE 0 END
) as total
from Orders
group by customer_id
having total > 1;
Orders table
+------------+-------------+--------------+
| order_id | customer_id | product_name |
+------------+-------------+--------------+
| 10 | 1 | A |
| 20 | 1 | B |
| 30 | 1 | D |
| 40 | 1 | C |
| 50 | 2 | A |
| 60 | 3 | A |
| 70 | 3 | B |
| 80 | 3 | D |
| 90 | 4 | C |
+------------+-------------+--------------+
Result
+-------------+-------+
| customer_id | total |
+-------------+-------+
| 3 | 2 |
+-------------+-------+
