sql SUM: change null with 0

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

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.

Advertisement

Answer