How to select fields using alias name?

Question

I have an select:

SELECT 
tb_dim_equipe.no_equipe "EQUIPE", 
tb_dim_profissional.no_profissional "PROFISSIONAL", 
no_cidadao "CIDADÃO", 
a.nu_cns "CNS", 
sum(case when co_dim_tempo >= 20190100 and co_dim_tempo <= 20200131 then 1 else 0 end) as "TOTAL" 
from (
SELECT no_cidadao, 
tb_fat_cad_individual.nu_cns, 
tb_fat_cad_individual.co_dim_profissional, 
tb_fat_cad_individual.co_dim_equipe from tb_fat_cidadao_pec 
join tb_fat_cad_individual on tb_fat_cad_individual.nu_cns = tb_fat_cidadao_pec.nu_cns 
join tb_fat_cidadao on tb_fat_cad_individual.co_seq_fat_cad_individual = tb_fat_cidadao.co_fat_cad_individual 
where st_mudou = 0 
and st_vivo = 1 
and st_gestante = 1 
and tb_fat_cidadao.co_dim_tempo_validade = 30001231) a 

left join (
SELECT tb_fat_atendimento_individual.nu_cns, 
tb_fat_atendimento_individual.co_dim_tempo 
from tb_fat_atendimento_individual 
join tb_dim_tempo on tb_dim_tempo.co_seq_dim_tempo = tb_fat_atendimento_individual.co_dim_tempo 
where co_seq_dim_tempo >= 20190100 
and co_seq_dim_tempo <= 20200131 
and ds_filtro_ciaps like '%ABP001%'

union SELECT tb_fat_proced_atend.nu_cns, 
tb_fat_proced_atend.co_dim_tempo 
from tb_fat_proced_atend 
join tb_dim_tempo on tb_dim_tempo.co_seq_dim_tempo = tb_fat_proced_atend.co_dim_tempo 
where co_seq_dim_tempo >= 20190100 
and co_seq_dim_tempo <= 20200131 
and ds_filtro_procedimento like '%0301010110%') b 
on a.nu_cns = b.nu_cns 
join tb_dim_equipe on tb_dim_equipe.co_seq_dim_equipe = a.co_dim_equipe 
join tb_dim_profissional on tb_dim_profissional.co_seq_dim_profissional = a.co_dim_profissional 
group by no_equipe, no_profissional, no_cidadao, a.nu_cns 
order by no_equipe, no_profissional, no_cidadao

This return to me five columns EQUIPE, PROFISSIONAL, CIDADÃO, CNS and TOTAL

I want to create a new select command like this:

SELECT sum(c.TOTAL) from *query above* c

but I receive the error that column not exists.

How I can use a select with the names of alias columns EQUIPE, PROFISSIONAL, CIDADÃO, CNS and TOTAL?

Answer 1

You quoted your column aliases:

SELECT 
tb_dim_equipe.no_equipe "EQUIPE", 
tb_dim_profissional.no_profissional "PROFISSIONAL", 
no_cidadao "CIDADÃO", 
a.nu_cns "CNS", 
sum(...) as "TOTAL" 

Postgres, unlike standard-compliant SQL databases, normalizes unquoted identifiers by converting them to lower case, such that the reference to c.TOTAL is equivalent to c.total. However, quoted identifiers like as "TOTAL" are not normalized and remain known to Postgres exactly as they are written.

You should either drop quotes in aliases (which I would recommend):

SELECT 
tb_dim_equipe.no_equipe AS equipe, 
tb_dim_profissional.no_profissional AS professional, 
no_cidadao AS cidadao, 
a.nu_cns AS cns, 
sum(...) AS total

or always reference quoted identifiers as quoted identifiers:

SELECT sum(c."TOTAL") from ... AS c