mysql add query with null value

Question

I have mysql query

SELECT
    ju.ID,
    ju.JENIS_USAHA,
    COALESCE(TOTAL_PEMBERI, 0) AS TOTAL_PEMBERI,
    COALESCE(OUTLET_PEMBERI, 0) AS OUTLET_PEMBERI,
    COALESCE(TOTAL_PENERIMA, 0) AS TOTAL_PENERIMA,
    COALESCE(OUTLET_PENERIMA, 0) AS OUTLET_PENERIMA,
    COALESCE(OUTLET_PEMBERI + OUTLET_PENERIMA, 0) AS SEMUA

the problem is when OUTLET_PEMBERI not null and OUTLET_PENERIMA null (or vise versa), SEMUA will show 0. How to solve this? Thank you

Tim Biegeleisen · Accepted Answer · 2018-08-16 06:16:33Z

1

Try using COALESCE on both terms separately:

SELECT
    ju.ID,
    ju.JENIS_USAHA,
    COALESCE(TOTAL_PEMBERI, 0) AS TOTAL_PEMBERI,
    COALESCE(OUTLET_PEMBERI, 0) AS OUTLET_PEMBERI,
    COALESCE(TOTAL_PENERIMA, 0) AS TOTAL_PENERIMA,
    COALESCE(OUTLET_PENERIMA, 0) AS OUTLET_PENERIMA,
    COALESCE(OUTLET_PEMBERI, 0) + COALESCE(OUTLET_PENERIMA, 0) AS SEMUA
FROM ...

If this doesn't solve your problem, and you really want a zero value when either term in the summation be NULL, then just leave your code as is.

answered Aug 16, 2018 at 6:16

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql add query with null value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related