Column Aliases in HAVING: BigQuery vs ClickHouse vs Postgres vs DuckDB

Quick Answer: Can You Use a Column Alias in a HAVING Clause?

It depends on the engine, and the four most common analytics engines give four different answers.

• PostgreSQL: no. SELECT aliases are not in scope in HAVING at all.
• DuckDB: yes, but only as a fallback when the name does not resolve otherwise, and never inside an aggregate function's arguments.
• BigQuery: yes, and the alias takes precedence over a same-named column from the FROM clause.
• ClickHouse: yes, and aliases are global, substituted anywhere the name appears, including WHERE.

The consequence: giving an aggregate the same name as the column it aggregates (MAX(x) AS x) and then referencing that name in HAVING is a hard error on BigQuery and ClickHouse, and runs fine on PostgreSQL and DuckDB. Worse, there are nearby queries where all four run and two of them quietly return different rows.

The Short Version

Every cell executed on 2026-08-13 against PostgreSQL 16.13 and 18.4, DuckDB 1.5.2, ClickHouse 26.7, and BigQuery (dry run plus live execution on a real project). Nothing in this table is inferred from documentation.

Why This Happens At All

The mechanism is logical query processing order:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

Aliases are created in SELECT. WHERE, GROUP BY and HAVING are all evaluated before SELECT, so the alias does not exist yet when those clauses run. That is why PostgreSQL says no, and why every other behaviour here is a vendor extension rather than a dialect choice between equals.

ORDER BY is the only clause evaluated after SELECT. That is why it is the only clause the standard could define alias behaviour for, and why all four engines agree on it.

To be precise, the standard defines this through scoping rules over the grouped table rather than prescribing a literal execution order, and processing order is the teaching version of it. The effect is the same. The divergence is not that the standard declined to specify aliases in HAVING; it specified a scope in which the question cannot arise, and then four vendors each added a convenience extension on top.

A Fixture You Can Run

Paste this into any of the four. No tables, no setup.

WITH ranked AS (
  SELECT 'shoes' AS term, 'mobile' AS device, 1 AS ranking_page_count
  UNION ALL SELECT 'shoes', 'desktop', 3
  UNION ALL SELECT 'boots', 'mobile', 1
  UNION ALL SELECT 'socks', 'desktop', 5
)
SELECT term, MAX(ranking_page_count) AS ranking_page_count
FROM ranked
GROUP BY term
HAVING MAX(ranking_page_count) >= 2
ORDER BY term

What you get:

ranked has a column called ranking_page_count. The SELECT list creates an alias with the same name on top of MAX() of that column. Then HAVING uses the name a third time, inside another MAX(). Three uses of one name, and that is the entire bug.

The two engines that run it return the correct answer, not a coincidentally-correct one. Ground truth per term is shoes=3, boots=1, socks=5, so >= 2 should keep shoes and socks. PostgreSQL and DuckDB never consult the alias inside an aggregate's arguments, so there is only one reading available to them and it is the intended one.

PostgreSQL: Aliases Are Not in Scope in HAVING

PostgreSQL runs the query, for the strictest possible reason. The SELECT documentation says each column in a HAVING condition "must unambiguously reference a grouping column, unless the reference appears within an aggregate function." Output aliases are not visible there, so MAX(ranking_page_count) can only mean the base column. There is no ambiguity to resolve.

Use the bare name instead and PostgreSQL tells you what it thinks you wrote:

HAVING ranking_page_count >= 2
-- ERROR: column "ranked.ranking_page_count" must appear in the GROUP BY clause
-- or be used in an aggregate function

PostgreSQL also documents the one alias inconsistency the standard is responsible for, and is blunt about it. GROUP BY prefers the input column: "In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name." ORDER BY does the opposite, and the docs add: "This inconsistency is made to be compatible with the SQL standard."

DuckDB: Aliases Are a Fallback, Never Inside an Aggregate

DuckDB also runs the query and returns the right answer, which is why local testing waves it through.

DuckDB lists "column aliases in WHERE, GROUP BY, and HAVING" as a friendly SQL feature, which sounds like what BigQuery does. It isn't. In DuckDB the alias is a fallback rather than an override. MAX(ranking_page_count) binds the base column, that is a legal aggregate, and the alias never gets consulted.

Two probes narrow it down. A name that exists only as an alias, used inside an aggregate, fails:

SELECT term, MAX(ranking_page_count) AS rpc
FROM ranked GROUP BY term
HAVING MAX(rpc) >= 2
-- Binder Error: Referenced column "rpc" not found in FROM clause!
-- Candidate bindings: "ranking_page_count", "device"

The same alias in a scalar expression resolves fine:

SELECT term, COUNT(*) AS n
FROM ranked GROUP BY term
HAVING abs(n) >= 2   -- runs

Empirically, then, DuckDB will not substitute an alias inside an aggregate function's arguments. I am describing observed behaviour across those two probes, not a documented rule, so treat it as a reliable pattern rather than a guarantee.

BigQuery: SELECT Aliases Override FROM Columns

BigQuery rejects the fixture:

Aggregations of aggregations are not allowed at [10:12]

That offset lands on ranking_page_count inside the HAVING clause, which is a useful confirmation of what it resolved.

The GoogleSQL query syntax documentation covers it in two places. On scope: "Aliases in the SELECT list are visible only to the following clauses: GROUP BY clause, ORDER BY clause, HAVING clause." On precedence: "If a query contains aliases in the SELECT clause, those aliases override names in a FROM clause."

Override, not fall back. Inside HAVING, ranking_page_count is the alias, the alias is MAX(ranking_page_count), so what BigQuery received was MAX(MAX(ranking_page_count)).

The docs also carry an ambiguity carve-out that reads like it might contradict this: a name is not ambiguous in GROUP BY, ORDER BY or HAVING "if it's both a column name and a SELECT list alias, as long as the name resolves to the same underlying object." Read as a contrapositive, that suggests a shadowing alias should raise Name ... is ambiguous. It does not. In every shape I tried, which was HAVING, GROUP BY and ORDER BY with a shadowing alias, precedence won and no ambiguity error appeared. GoogleSQL does raise ambiguity errors in other shapes, so I would not extend that beyond the three clauses I probed. Aliasing a column to its own name is harmless; aliasing something else to that column's name silently takes over.

ClickHouse: Aliases Are Global and Substituted Everywhere

ClickHouse rejects it too, with a different flavour of the same complaint:

Code: 184. DB::Exception: Aggregate function MAX(ranking_page_count) AS ranking_page_count
is found inside another aggregate function in query. (ILLEGAL_AGGREGATION)

The ClickHouse syntax documentation is upfront: "Aliases are global for a query or subquery, and you can define an alias in any part of a query for any expression." Then the warning: "Be careful with aliases that are the same as column or table names."

Global means global. ClickHouse substitutes the alias wherever the name appears, so it fails even when the alias shadows nothing:

SELECT term, max(ranking_page_count) AS rpc
FROM ranked GROUP BY term
HAVING max(rpc) >= 2   -- same ILLEGAL_AGGREGATION

ClickHouse is the only one of the four with a setting for this. prefer_column_name_to_alias = 1 makes column names beat aliases, and the fixture then runs.

Do not reach for that as a migration switch, though. Turning it on globally changes the meaning of every currently working query on that instance that relies on alias substitution, including the ones nobody is looking at, and it changes them silently rather than breaking them loudly. Set it per query or per profile, on queries you are actively porting.

When It Goes Silent

Everything above is loud. An engine either returns the right rows or refuses. The dangerous cases are the ones where all four run and two of them disagree, and there are two you can reproduce against the fixture above.

The first is in GROUP BY:

SELECT device AS term, COUNT(*) AS n
FROM ranked
GROUP BY term, device
ORDER BY 1, 2

PostgreSQL and DuckDB read term in GROUP BY as the base column, so they group by (term, device) and return 4 rows, one per term-device pair, every count 1. BigQuery and ClickHouse read it as the alias, so they group by (device, device), which collapses to device, and return 2 rows with counts of 2. Nobody raises anything.

The second is in HAVING, and it needs the alias to be a non-aggregate expression so there is no nested aggregate to trip the error:

SELECT term, ranking_page_count * 10 AS ranking_page_count
FROM ranked
GROUP BY term, ranking_page_count
HAVING MAX(ranking_page_count) > 4
ORDER BY term, 2

That second one is the case worth internalising. The nested-aggregate rule that makes the headline bug so loud is exactly what is missing here, and without it the disagreement just shows up as a different number on a dashboard.

Why the Four Engines Disagree

Sort this into two camps, alias-first and column-first, and that mostly holds. It is really four points on one axis: how much authority a SELECT alias gets. PostgreSQL gives it the least, none in HAVING and none in WHERE. DuckDB gives it a little, usable in three clauses but only when normal binding fails and never inside an aggregate. BigQuery gives it real precedence in GROUP BY, ORDER BY and HAVING. ClickHouse gives it the entire query.

Note that all four agree in two rows of the table, not one. They agree on ORDER BY, for the processing-order reason above, and they agree on the fully qualified form, which is the whole reason it works as a fix.

How to Fix It

In order of how much I would trust each one.

1. Don't shadow. Name the aggregate something the base column isn't called:

SELECT term, MAX(ranking_page_count) AS max_ranking_pages
FROM ranked GROUP BY term
HAVING MAX(ranking_page_count) >= 2

There is no ambiguity for anyone to resolve differently. This is the actual fix and it costs one word.

2. Push the filter to an outer query. No alias-in-HAVING semantics are involved at all:

SELECT * FROM (
  SELECT term, MAX(ranking_page_count) AS max_ranking_pages
  FROM ranked GROUP BY term
) WHERE max_ranking_pages >= 2

This is the most portable option in the post. It works on all four here and does not depend on any vendor extension, so it should hold on engines I did not test.

3. Qualify the reference. Useful as a retrofit when you can't touch the SELECT list:

HAVING MAX(ranked.ranking_page_count) >= 2

I verified this exact statement, qualified against a CTE rather than a base table, on all four including ClickHouse. It is the weakest of the three though: add a FROM alias, inline the CTE, or wrap it in a join and the qualification either breaks or quietly stops disambiguating. It also looks redundant enough that someone will delete it, and "leave a comment" is a social fix for a structural problem.

Error Messages and What They Mean

Appendix: MySQL

MySQL comes up constantly in searches for this, so for the record, tested separately on MySQL 8.4.11 and deliberately left out of the comparison table above. MySQL runs the fixture and returns the same rows as PostgreSQL and DuckDB. It allows a bare alias in HAVING, unlike PostgreSQL, rejects an alias in WHERE, and sits with the column-first camp on the shadowing cases. I did not test Snowflake, Redshift, Trino, Spark or SQL Server, so this post says nothing about them.

FAQ

Can you use a column alias in a HAVING clause?

On BigQuery, ClickHouse and DuckDB, yes. On PostgreSQL, no, because SELECT output aliases are not in scope in HAVING. The ones that allow it do not agree on what happens when the alias shares a name with a column from the FROM clause, so alias references in HAVING are not portable.

Why does BigQuery say "Aggregations of aggregations are not allowed"?

Usually because a SELECT alias shadows the column it was built from. If you write MAX(x) AS x and then reference x in HAVING, BigQuery resolves the name to the alias, which expands to MAX(MAX(x)). Rename the alias to max_x, or qualify the reference as MAX(table.x).

What causes ClickHouse error code 184, ILLEGAL_AGGREGATION?

An aggregate function ended up inside another aggregate function. Because ClickHouse aliases are global, this usually happens through alias substitution rather than anything you wrote literally, and it can fire even when the alias does not shadow a real column.

Why does the same query work in DuckDB but fail in BigQuery?

Alias precedence. When a name is both a base column and a SELECT alias, DuckDB binds the base column and BigQuery binds the alias. Developing on DuckDB and deploying to BigQuery hides this class of bug until deployment.

Test Against the Dialect You Deploy To

The timeline on my end was straightforward. I was adding a BigQuery dry-run check to a project I develop against DuckDB locally. The first run flagged two reports that my DuckDB tests had been passing for weeks, so I fixed both before they ever ran on BigQuery.

Being precise about what that verified: the 137 and 41 row counts were identical before and after the fix on DuckDB, which shows the fix did not change the logic. On BigQuery the fixed queries pass a dry run, which shows they now parse and type-check where they previously errored. I did not execute them live on BigQuery, so I cannot claim BigQuery returns those same 137 and 41 rows. Portability of the syntax is proven; equality of the results across engines is not, and if that mattered for a report I would go run it.

A dry run scans no bytes, so the per-run cost really is zero. It is not free to set up: you need BigQuery credentials in CI, and you need typed stubs, meaning empty tables carrying the production schema so the planner can resolve column types without touching real data. That was an afternoon of work for 43 statements. Cheap, not free.

To be clear about what tooling does and does not solve here: Bruin does not transpile between dialects, and this bug would have shipped through it unchanged. What it does is make the dialect check part of the pipeline definition. bruin validate checks pipeline configuration and, on BigQuery and Snowflake, automatically runs a dry-run version of each query against the destination platform to confirm it is valid there. Its --fast flag skips exactly that query validation, which tells you where the real cost of the check sits. Any equivalent step in your own CI would have caught this just as well.

Helpful Links

• BigQuery GoogleSQL query syntax, including alias visibility and ambiguous aliases
• PostgreSQL SELECT reference, covering HAVING, GROUP BY, and ORDER BY name resolution
• ClickHouse syntax reference on expression aliases
• DuckDB friendly SQL, including column aliases in WHERE, GROUP BY, and HAVING
• bruin validate for wiring destination-platform query validation into CI