AWS Athena CAST with GROUP BY

user_6205503 0
Viewed 15708

I have a table in Athena AWS with a timestamp field. I want to group them by date. My SQL query is:

SELECT CAST(createdat AS DATE) FROM conversations GROUP BY createdat But my result is the following: asd

As you can see the group by does not work, and the reason is that the new table has the name field _col0 instead createdat. I also tried: SELECT CAST(createdat AS DATE) FROM conversations GROUP BY _col0 but I got and error.

Does anybody has any suggestions? I will appreciate it

amazon-athena amazon-web-services sql
3 Answers

Same but a little shorter

SELECT CAST(createdat AS DATE) 
FROM conversations 
GROUP BY 1;
user_75702880

I know the question is pretty old but would like to answer the reason why it failed at the first place.

Athena follows the same standard ANSI SQL convention, which defines a standard in processing an SQL query. Below is the order of processing

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

From above, we can see the GROUP BY clause is processed before the SELECT clause, so the alias is not known when the GROUP BY clause is evaluated.

Any of other two answers i.e. either a column number (AFAIK it starts from 1) or full function CAST(createdat AS DATE) will work.

SELECT CAST(createdat AS DATE) 
FROM conversations 
GROUP BY 1;

OR

SELECT CAST(createdat AS DATE) 
FROM conversations 
GROUP BY CAST(createdat AS DATE);
user_42555550
Related