sql SUM: change null with 0
postgresql, sql
Solution
In general you would first set `NULL` values to `0` and then `SUM` them:
SELECT SUM(COALESCE(bgrealisasi0.n_nilai, 0)) ...
Otherwise your queries have a few deficiencies such as `field LIKE '%'` which is a really inefficient way of writing `field IS NOT NULL`. The use of a scalar sub-query makes the overall query really difficult to read and the joins can probably be optimized if rewritten as a sub-query instead.
Problem
I have a query like this: ``` SELECT bgdepartemen.c_kodedept AS c_kodedept, bgdepartemen.vc_namadept AS vc_namadept, bgdivisi.c_kodediv AS c_kodediv, bgdivisi.vc_namadiv AS vc_namadiv, bggroup0.c_kodegrp0 AS c_kodegrp0, bggroup0.vc_namagrp0 AS vc_namagrp0, (SELECT COALESCE(SUM(bgbudget0.n_nilai)) FROM (bgbudget0 JOIN bggroup1 ON bgbudget0.c_kodegrp1 = bggroup1.c_kodegrp1) WHERE bgbudget0.n_tahun = 2016 AND bgbudget0.n_bulan >= 2 AND bgbudget0.n_bulan <= 3 AND bgbudget0.c_kodediv = bgdivisi.c_kodediv AND bggroup1.c_kodegrp0 = bggroup0.c_kodegrp0 AND bggroup1.c_kodegrp1 LIKE '%') AS nilai FROM bgdivisi JOIN bggroup0 ON 1 = 1 JOIN bgdepartemen on bgdivisi.c_kodedept = bgdepartemen.c_kodedept WHERE bgdivisi.c_kodediv LIKE '%' AND bgdepartemen.c_kodedept LIKE '%' AND bggroup0.c_kodegrp0 LIKE '%' ``` And another like this: ``` SELECT bgdepartemen.c_kodedept AS c_kodedept, bgdepartemen.vc_namadept AS vc_namadept, bgdivisi.c_kodediv AS c_kodediv, bgdivisi.vc_namadiv AS vc_namadiv, bggroup0.c_kodegrp0 AS c_kodegrp0, bggroup0.vc_namagrp0 AS vc_namagrp0, (SELECT COALESCE(SUM(bgrealisasi0.n_nilai)) FROM (bgrealisasi0 JOIN bggroup1 ON bgrealisasi0.c_kodegrp1 = bggroup1.c_kodegrp1) WHERE bgrealisasi0.n_tahun = 2016 AND bgrealisasi0.n_bulan >= 2 AND bgrealisasi0.n_bulan <= 3 AND bgrealisasi0.c_kodediv = bgdivisi.c_kodediv AND bggroup1.c_kodegrp0=bggroup0.c_kodegrp0 AND bggroup1.c_kodegrp1 LIKE '%') AS nilai FROM bgdivisi JOIN bggroup0 ON 1 = 1 JOIN bgdepartemen on bgdivisi.c_kodedept = bgdepartemen.c_kodedept WHERE bgdivisi.c_kodediv LIKE '%' AND bgdepartemen.c_kodedept LIKE '%' AND bggroup0.c_kodegrp0 LIKE '%' ``` I want to select from those tables, with condition ``` WHERE a.c_kodedept = b.c_kodedept AND a.c_kodediv = b.c_kodediv AND a.c_kodegrp0 = b.c_kodegrp0 AND (a.nilai is not null or b.nilai is not null) ``` But I also want to change the null record which appears with 0, I've tried `COALESCE` but it still doesn't give me the right result.