CODEWITHSUNDEEP
mysqlsqlselectnull
Maytham Fahmi
Maytham Fahmi

Reputation: 33387

MySQL replace NULL to some value

I have the following stored procedure

BEGIN
SELECT kids.*, SUM(point) as `point_sum`
FROM kids
    LEFT JOIN tasks
    ON kids.id = tasks.kid_id
WHERE kids.user_id = IN_user_id
GROUP BY kids.name;
END

This statement works fine.

My Question: the SUM(point) for new users are typically NULL because there is no submitted value yet to be summed. What I want is if SUM(point) is NULL then it should return value like 0 but otherwise it should present the sum. I have looked around and not sure how to fix it, any good ideas?

Upvotes: 1

Views: 166

Answers (2)

Mureinik
Mureinik

Reputation: 311123

You could use the coalesce function:

SELECT kids.*, COALESCE(SUM(point), 0) as `point_sum`
FROM kids
    LEFT JOIN tasks
    ON kids.id = tasks.kid_id
WHERE kids.user_id = IN_user_id
GROUP BY kids.name;

Upvotes: 1

tadman
tadman

Reputation: 211560

All you really need is IFNULL():

SELECT kids.*, IFNULL(SUM(point), 0) AS point_sum

That converts NULL to the supplied value, in this case 0.

Upvotes: 1

Related Questions