avoid NULL reference result in MySql

Question

I am running this query. Works fine however if NULL result found then it creates exception error in my application. How can I avoid NULL Reference

SELECT SUM(pointsEarned) as earned, SUM(pointsRedeemed) as redeemed 
FROM `salesData` 
WHERE dealerID = @dealerID and status = 'active'

Look at this: w3resource.com/mysql/comparision-functions-and-operators/… — Mitat Koyuncu
– Mitat Koyuncu, Commented Nov 10, 2017 at 10:42
Do you mean pointsEarned ,pointsRedeemed can be NULL ? If so, look at this answer showing an example using COALESCE stackoverflow.com/a/13132245/448144 — Nope
– Nope, Commented Nov 10, 2017 at 10:43
Possible duplicate of Why does Sql Sum of multiple columns containing nulls return incorrect result? — Nope
– Nope, Commented Nov 10, 2017 at 12:48

Matt · Accepted Answer · 2017-11-10 10:48:48Z

1

Use COALESCE Around your SUM.

SELECT COALESCE(SUM(pointsEarned),0) earned, COALESCE(SUM(pointsRedeemed),0) redeemed 
FROM salesData 
WHERE dealerID = @dealerID
AND status = 'active'

answered Nov 10, 2017 at 10:48

Matt

15.1k28 gold badges109 silver badges161 bronze badges

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

Collectives™ on Stack Overflow

avoid NULL reference result in MySql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related