how to sum in Postgres

Question

I have the data I'm querying

total_a | total_b | sum |
1200       500       ?

this my query

select 
    ROUND(SUM(CASE WHEN status= 0 THEN total_budget ELSE 0 END)) AS total_a,
    ROUND(SUM(CASE WHEN status= 1  THEN total_sisa_tahun_lalu ELSE 0 END)) AS total_b,
    SUM(COALESCE(total_a,0) + COALESCE(total_b,0))
from product

my query didn't succeed in summing the alias alias

score 1 · Accepted Answer · answered Jun 16 '21 at 01:56

You cannot reference a column alias in the SELECT where it is defined. The reason is simple: SQL does not guarantee the order of evaluation of expressions in the SELECT.

You can use a CTE, subquery, or repeat the expression:

select ROUND(SUM(CASE WHEN status= 0 THEN total_budget ELSE 0 END)) AS total_a,
       ROUND(SUM(CASE WHEN status= 1  THEN total_sisa_tahun_lalu ELSE 0 END)) AS total_b,
       SUM(CASE WHEN status = 0 THEN total_budget
                WHEN status = 1 THEN total_sisa_tahun_lalu
                ELSE 0
           END)
from product

how to sum in Postgres

1 Answers1