I need a function like countif in PostgreSQL-CodePudding

I have this table like this

Status    Last_update_time
4      | 2/2/2021, 11:03
5      | 2/2/2021, 12:04
4      | 24/2/2021, 12:36
4      | 20/2/2021, 17:33
5      | 13/2/2021, 10:57

I already write a SQL like this

Select date_trunc('week', CAST(kyc.last_update_time as timestamp )) AS "week",
       Count(status)
From KYC
Where kyc.status = 4 
  and (kyc.last_update_time >= date_trunc('week', CURRENT_TIMESTAMP - interval '3 week') 
       and kyc.last_update_time < date_trunc('week', CURRENT_TIMESTAMP))
Group by 1

So it will count for me all status 4 for each week in last 3 week

But now I want to change that I can count 4 and 5. And when I visualization, I will have 2 columns, 1 for status 4, 1 for status 5

My expected output SQL like this

Select date_trunc('week', CAST(kyc.last_update_time as timestamp )) AS "week",
       Count(if(kyc.status = 4,kyc.used_id,null) as XTTC
       Count(if(kyc.status = 5,kyc.used_id,null) as XTTB
From KYC 

Where (kyc.last_update_time >= date_trunc('week', CURRENT_TIMESTAMP - interval '3 week') 
       and kyc.last_update_time < date_trunc('week', CURRENT_TIMESTAMP))
Group by 1

Is there any function like countif in excel that can help me with this

Thank you

CodePudding user response：

As @a_horse_with_no_name suggests you can use conditional aggregation with filter as a very capable alternative of countif in order to achieve this.

select date_trunc('week', last_update_time::timestamp) as "week", 
       count(*) filter (where status = 4) as "XTTC",
       count(*) filter (where status = 5) as "XTTB"
from kyc
where last_update_time >= date_trunc('week', CURRENT_TIMESTAMP - interval '3 weeks') 
  and last_update_time <  date_trunc('week', CURRENT_TIMESTAMP)
group by 1;