7 / 13 SQL Fundamentals

The Alias Is Not There Yet

Same readings table as before. A teammate writes this and it does not do what they expect.

SELECT sensor, avg(value) AS mean
FROM readings
WHERE mean > 12
GROUP BY sensor;

What actually happens?

A

It works, but filters individual readings above 12 before averaging, so sensor a comes back as 20.

B

The statement fails. mean does not exist as a column at the time WHERE runs, and an aggregate cannot be evaluated there either. The condition belongs in HAVING avg(value) > 12.

C

It returns only sensor a. PostgreSQL substitutes the select-list expression wherever the alias appears.

D

It returns both sensors. WHERE cannot see the alias, so the condition is skipped with a warning.