sql SUM: change null with 0

Question

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.

Answer 1

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.

Answer 2

You should use

SELECT COALESCE(SUM(bgbudget0.n_nilai), 0) 
FROM .....
SELECT COALESCE(SUM(bgrealisasi0.n_nilai), 0) 
FROM ....