In AWS Athena I have the SQL query as follows:

select licence, count(distinct (id)) as amount
from "database_name" 
where YEAR(column_year) = 2021
group by licence
having amount > 10
order by amount desc

*Then I get the error:

SYNTAX_ERROR: line 5:8: Column 'amount' cannot be resolved. This query ran against the "database_name", unless qualified by the query.*

What am I doing wrong?

CodePudding user response：

2 Things.

1. You cannot use aliases in Having clause, So you have to use exact column calculation.
2. Distinct is not a function, So you can use it without parenthesis.

select licence, count(distinct id) as amount
from "database_name" 
where YEAR(column_year) = 2021
group by licence
having count(distinct id) > 10
order by amount desc