MySQL replace NULL to some value

Question

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?

Use IFNULL or COALESCE -- coalesce(sum(point),0)...

sgeddes
– sgeddes

2014-12-04 17:31:58 +00:00
Commented Dec 4, 2014 at 17:31 — sgeddes
– sgeddes, Commented Dec 4, 2014 at 17:31

Mureinik · Accepted Answer · 2014-12-04 17:33:59Z

1

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;

answered Dec 4, 2014 at 17:33

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

Comments

tadman · Accepted Answer · 2014-12-04 17:32:45Z

1

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.

answered Dec 4, 2014 at 17:32

tadman

212k23 gold badges237 silver badges266 bronze badges

Collectives™ on Stack Overflow

MySQL replace NULL to some value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related