CAST a date in Presto to next count

0
votes

I would like to query Athena with JSON files. I matched creation_date with id because I would like to get a heatmap where on Y axis I have month, on X axis there day and I count the id's inside. I created a table with 2 columns: creation_date date, id int. Next I am query with the below code:

SELECT CAST(creation_date as DATE) as ad_creation, 
       COUNT(id) as Total_ads 
FROM default.test 
GROUP BY CAST(creation_at_first as DATE)

unfortunately I am getting this error:

DatabaseError: Execution failed on sql: SELECT CAST(creation_date as DATE) as ad_creation, COUNT(id) as Total_ads FROM default.testing_fresh_1 GROUP BY CAST(creation_date as DATE)

When I query Select * from...

I get results formatted like this:

creation_date   
2018-07-01 02:02:09 
2018-06-05 01:39:30 
2018-05-16 21:28:48 
2017-04-23 17:03:53

Any idea what I am doing wrong?

2
sqlamazon-athenaprestoamazon-sagemaker

2 Answers

1
votes

From your select * result set, I guess there isn't ID column in your table.

You can try to use COUNT(*) instead of COUNT(id)

SELECT CAST(creation_date as DATE) as ad_creation, 
      COUNT(*) as Total_ads 
FROM default.test 
GROUP BY CAST(creation_date as DATE)
1
votes

Try below Code.

SELECT CAST(creation_date as DATE) as ad_creation, 
        COUNT(id) as Total_ads 
FROM default.testing_fresh_1 
GROUP BY ad_creation