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 ...

no you can avoid it, you can only make a cte – nbk Dec 01 '22 at 23:12 — nbk, Dec 01 '22 at 23:12
I would love to know what is the problem of this question. – anvd Dec 01 '22 at 23:27 — anvd, Dec 01 '22 at 23:27

score 1 · Accepted Answer · answered Dec 01 '22 at 23:14

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

answered Dec 01 '22 at 23:14

nbk

45,398
8
30
47

Count over previously defined alias

1 Answers1