3 ms·

Are there databases where the alias defined in the select part of the query is available in the group by/having section?

• simondotau 5y ago
  MySQL/MariaDB certainly permits their use in GROUP BY, HAVING and ORDER BY.
• MrDOS 5y ago
  Postgres reference SELECTed aliases in ORDER BY/GROUP BY clauses, but _not_ in HAVING: test=# create table event (who text not null, test(# what text not null, test(# at timestamptz not null default now()); CREATE TABLE test=# insert into event (who, what) test-# values ('Bob', 'sent a message'), test-# ('Alice', 'received a message'), test-# ('Alice', 'sent a message receipt'), test-# ('Bob', 'received the message receipt'), test-# ('Bob', 'waited patiently for a response'), test-# ('Bob', 'worried whether his message had been well-received'), test-# ('Bob', 'paced'), test-# ('Alice', 'forgot to reply for two weeks'), test-# ('Bob', 'began to panic'), test-# ('Bob', 'fled the country for Moldova in a fit of panic'), test-# ('Alice', 'finally remembered to reply'), test-# ('Bob', 'never got it'); INSERT 0 12 test=# select who as "The poor sod", test-# count(what) as "Events" test-# from event test-# group by "The poor sod" test-# order by "Events" desc; The poor sod | Events --------------+-------- Bob | 8 Alice | 4 (2 rows)
• jameshart 5y ago
  Really varies by implementation. Snowflake supports it - https://docs.snowflake.com/en/sql-reference/constructs/having.html https://docs.snowflake.com/en/sql-reference/constructs/havin... Redshift doesn’t: https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clau... Databricks doesn’t: https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select-having.html https://docs.databricks.com/sql/language-manual/sql-ref-synt... All of them do also support teradata’s QUALIFY clause which is like another layer over WHERE and HAVING that filters windowed data.