CODEWITHSUNDEEP
sqlpostgresqlselectsum
karthick
karthick

Reputation: 11

postgresql query to replace 0 with "Null"

I'm working on reporting, the problem is that I'm getting null values instead of 0. Since I managed to get this report month wise, I'm stuck proceeding further with this query. Help me if i can add any changes. Thanks in advance.

select sub.district_name,sub.service_name,service_code, 
        sum(Approved_count) filter (where Month_ = 1) AS "January approved" ,
        sum(Rejected_count) filter (where Month_ = 1) AS "January Rejected", 
        sum(Approved_count) filter (where Month_ = 2) AS "February approved" ,
        sum(Rejected_count) filter (where Month_ = 2) AS "February Rejected", 
        sum(Approved_count) filter (where Month_ = 3) AS "March approved" ,
        sum(Rejected_count) filter (where Month_ = 3) AS "March Rejected", 
        sum(Approved_count) filter (where Month_ = 4) AS "April approved" ,
        sum(Rejected_count) filter (where Month_ = 4) AS "April Rejected", 
        sum(Approved_count) filter (where Month_ = 5) AS "May approved" ,
        sum(Rejected_count) filter (where Month_ = 5) AS "May Rejected", 
        sum(Approved_count) filter (where Month_ = 6) AS "June approved" ,
        sum(Rejected_count) filter (where Month_ = 6) AS "June Rejected",
        sum(Approved_count) filter (where Month_ = 7) AS "July approved" ,
        sum(Rejected_count) filter (where Month_ = 7) AS "July Rejected",
        sum(Approved_count) filter (where Month_ = 8) AS "Auguest approved" ,
        sum(Rejected_count) filter (where Month_ = 8) AS "Auguest Rejected",
        sum(Approved_count) filter (where Month_ = 9) AS "September approved" ,
        sum(Rejected_count) filter (where Month_ = 9) AS "September Rejected",
        sum(Approved_count) filter (where Month_ =10) AS "October approved" ,
        sum(Rejected_count) filter (where Month_ =10) AS "October Rejected",
        sum(Approved_count) filter (where Month_ = 11) AS "November approved" ,
        sum(Rejected_count) filter (where Month_ = 11) AS "November Rejected",
        sum(Approved_count) filter (where Month_ = 12) AS "December approved" ,
        sum(Rejected_count) filter (where Month_ = 12) AS "December Rejected",
                sum(Approved_count)  "Total Approved Applications",
                sum(Rejected_count)  "Total Rejected Application"

        
        from
        (
        select district_name,service_name,sum(approved) as Approved_count, sum(rejected) as Rejected_count,service_code,cast(EXTRACT(month FROM status_upd_dt) as Numeric) as Month_   from dashboard
where cast(status_upd_dt as date) between '2019-01-01' and '2019-12-31' 
        group by district_name,service_name ,service_code,status_upd_dt order by district_name,service_code,status_upd_dt asc
        )sub
        group by sub.district_name,sub.service_name,service_code order by sub.district_name,service_code

Upvotes: 1

Views: 135

Answers (1)

Mureinik
Mureinik

Reputation: 311853

sum returns null if it sums nothing. You can explicitly handle this by using coalesce. E.g.:

coalesce(sum(Approved_count) filter (where Month_ = 1), 0) AS "January approved" ,

Upvotes: 2

Related Questions