Select with IF statement on postgresql

I have a code like that:

select
    tbl.person
    ,COUNT(distinct tbl.project)
    ,if (tbl.stage like '%SIGNED%') then sum(tbl.value) else '0' end if as test
from
    my_table tbl
group by
1

​x
 
select    tbl.person    ,COUNT(distinct tbl.project)    ,if (tbl.stage like '%SIGNED%') then sum(tbl.value) else '0' end if as testfrom    my_table tblgroup by1​​

And it returns me that error message:

SQL Error [42601]: ERROR: syntax error at or near "then"

 
SQL Error [42601]: ERROR: syntax error at or near "then"​

I didn’t got it. As I saw on documentation, the if statement syntax appears to be used correctly

Answer

In Postgres, I would recommend using filter:

select tbl.person, COUNT(distinct tbl.project)
       sum(tbl.value) filter (where tbl.stage like '%SIGNED%') as test
from my_table tbl
group by 1;

 
select tbl.person, COUNT(distinct tbl.project)       sum(tbl.value) filter (where tbl.stage like '%SIGNED%') as testfrom my_table tblgroup by 1;​

if is control flow logic. When working with queries, you want to learn how to think more as sets. So the idea is to filter the rows and add up the values after filtering.

Advertisement

Answer