CODEWITHSUNDEEP
sqlprestoamazon-athena
Joey Yi Zhao
Joey Yi Zhao

Reputation: 42664

How to filter on aggregation value in Athena (Presto)?

I can't run a simple sql select with where condition from Athena console. Below is the details:

SELECT name, count(*) AS c
FROM target_reddit_movie
GROUP BY name
HAVING c > 1
ORDER BY c DESC
LIMIT 10

I got an error SYNTAX_ERROR: line 1:74: Column 'c' cannot be resolved.

It looks like Athena doesn't recognise the as column name. How can I make it work? Does it use a different syntax?

Upvotes: 4

Views: 9007

Answers (1)

Lamanus
Lamanus

Reputation: 13591

The process is logically like this (execution engine is free to choose a different execution strategy):

  1. WHERE is applied (if any; there is no WHERE in your case, of course)
  2. GROUP BY aggregations are calculated
  3. HAVING is applied on aggregated values -- here you can refer to aggregates (e.g. count(*)) and GROUP BY columns (e.g. name in your case)
  4. SELECT assignments are applied (from now on c is known to mean count(*))
  5. ORDER BY is applied, hence it can use c
  6. LIMIT is applied

See also How does Group by and Having works and Presto documentation for SELECT

Upvotes: 5

Related Questions