SQL Converting sum of null values to 0

Question

I have a query in SQL which sum values and add them as months to a date.

    SELECT
    FROM_UNIXTIME(S.created) as start_date, 
   (FROM_UNIXTIME(S.created) + INTERVAL (C.items_left + C.items_given) MONTH)
    AS end_date,
    FROM table1 S
    LEFT JOIN table2 C ON C.id = S.id;

The problem arises when some of these values to sum are null instead of 0 and I am not able to change the source of data.

(FROM_UNIXTIME(S.created) + INTERVAL (C.items_left + C.items_given) MONTH)
AS end_date,

is generating nulls because (C.items_left + C.items_given) is null.

So, the question is, how do I execute this sum so the result is 0 instead of null?

coalesce should help

Vatev
– Vatev

2019-02-22 12:25:24 +00:00
Commented Feb 22, 2019 at 12:25 — Vatev
– Vatev, Commented Feb 22, 2019 at 12:25

Zaynul Abadin Tuhin · Accepted Answer · 2019-02-22 12:25:45Z

2

use coalesce

SELECT
    FROM_UNIXTIME(S.created) as start_date, 
   ( coalesce(FROM_UNIXTIME(S.created),0) + INTERVAL coalesce((coalesce(C.items_left,0) + coalesce(C.items_given,0)),0) MONTH)
    AS end_date,
    FROM table1 S
    LEFT JOIN table2 C ON C.id = S.id;

answered Feb 22, 2019 at 12:25

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

4 Comments

Belen Over a year ago

Anyway I just put it in the sum coalesce(C.items_left + C.items_given), as it's the only source of null data.

Zaynul Abadin Tuhin Over a year ago

@Belen you can it inner as well for both items_left and items_given

Zaynul Abadin Tuhin Over a year ago

@Belen inner is safe otherwise if items_left null and items_given has value in that case null+val also make it null

Belen Over a year ago

@zaybul, agreed, I'm going with your query, it works perfect as it is. Thanks!

Gordon Linoff · Accepted Answer · 2019-02-22 12:25:52Z

2

Use COALESCE():

(FROM_UNIXTIME(S.created) + INTERVAL (COALESCE(C.items_left, 0) + COALESCE(C.items_given, 0)) MONTH)

answered Feb 22, 2019 at 12:25

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Converting sum of null values to 0

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related