How to assign a field name to an SQL Count in AWS Athena SQL

I’m still new to Athena. I think I got my database defined correctly, as shown in Example 1 below. However, when I run a count query, I get results unlike what I would expect.

Example 1: Works Fine except count is called “_col3”

SELECT ticker, timestampmonth, timestampday, count(*) FROM "relatixcurrdayjson"."currday"
where ticker = 'TQQQ'
group by ticker, timestampmonth, timestampday
order by ticker, timestampmonth, timestampday  

Result:

Example 2: syntax error

SELECT ticker, timestampmonth, timestampday, count(*) as 'countperday' FROM "relatixcurrdayjson"."currday"
where ticker = 'TQQQ'
group by ticker, timestampmonth, timestampday
order by ticker, timestampmonth, timestampday 

This query shows a syntax error when I click “Run Query”:

Answer

Don’t use single quotes for identifiers. They are meant for literal strings only. If you need to quote an identifier, use double quotes; but here, you don’t even need to quote it, since it does not contain special characters.

So:

select ticker, timestampmonth, timestampday, count(*) as countperday 
from "relatixcurrdayjson"."currday"
where ticker = 'TQQQ'
group by ticker, timestampmonth, timestampday
order by ticker, timestampmonth, timestampday 

Redshift folds the identifiers as lower case by default. This means you don’t even need to quote the schema and table name in the from clause (since they are all lower case).