Count over previously defined alias

Question

I'm trying to avoid writing twice the EXTRACT condition. There is any way to do that? Count over the previous alias apparently is not valid.

SELECT EXTRACT(DECADE FROM to_date(released_year::text, 'yyyy')) AS decade, 
    count(decade) AS total_by_decade
FROM album ...

Answer 1

Basically you can avoit it completely, but you can make a CTE

WITH
 CTE as(
SELECT EXTRACT(DECADE FROM to_date(released_year::text, 'yyyy')) AS decade 
 
FROM album ...)
SELECT  decade,  count(decade) AS total_by_decade FROM CTe GROUP BY decade