You write SELECT first, but the database evaluates it almost last. This single fact explains most “why can't I use that here?” errors.

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

FROM + JOINs
Build the working set of rows from the tables.
WHERE
Keep rows whose condition is true. Aggregates don't exist yet.
GROUP BY
Collapse rows into one row per group.
HAVING
Keep groups whose condition is true — aggregates are available now.
SELECT
Compute output expressions, window functions and aliases.
DISTINCT
Remove duplicate output rows.
ORDER BY
Sort — can use aliases, since SELECT already ran.
LIMIT / OFFSET
Cut the sorted result.

Consequences

You try	Result	Why
`WHERE monthly > 9000` (alias)	Error in standard SQL	`WHERE` runs before `SELECT` creates the alias
`WHERE COUNT(*) > 2`	Error everywhere	Aggregates don't exist before `GROUP BY` — use `HAVING`
`ORDER BY monthly`	Works	`ORDER BY` runs after `SELECT`
`WHERE ROW_NUMBER() OVER (…) = 1`	Error	Window functions are computed in `SELECT` — filter in an outer query / CTE

SQL

SELECT dept_id, COUNT(*) AS headcount
FROM employees
WHERE COUNT(*) > 2
GROUP BY dept_id;

Error

misuse of aggregate: COUNT()

SQL

SELECT dept_id, COUNT(*) AS headcount
FROM employees
WHERE dept_id IS NOT NULL           -- row filter: before grouping
GROUP BY dept_id
HAVING COUNT(*) > 2                 -- group filter: after grouping
ORDER BY headcount DESC;            -- alias is fine here

Output

dept_id | headcount
--------+----------
1       | 4
2       | 3

SQL

-- to filter on a computed column, compute it in a subquery / CTE first
SELECT name, monthly
FROM (SELECT name, salary / 12 AS monthly FROM employees) AS t
WHERE monthly > 9000
ORDER BY monthly DESC;

Output

name  | monthly
------+--------
Asha  | 12500
Ravi  | 10000
Priya | 9166

What is the order of execution of a SQL query?