Andreas Chandra Gaozu Andreas Chandra Gaozu - 5 months ago 17
SQL Question

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.