Kevin Kevin - 28 days ago 7
MySQL Question

Return 0 if field is null in MySQL

In MySQL, is there a way to set the "total" fields to zero if they are NULL?

Here is what I have:

SELECT uo.order_id, uo.order_total, uo.order_status,
(SELECT SUM(uop.price * uop.qty)
FROM uc_order_products uop
WHERE uo.order_id = uop.order_id
) AS products_subtotal,
(SELECT SUM(upr.amount)
FROM uc_payment_receipts upr
WHERE uo.order_id = upr.order_id
) AS payment_received,
(SELECT SUM(uoli.amount)
FROM uc_order_line_items uoli
WHERE uo.order_id = uoli.order_id
) AS line_item_subtotal
FROM uc_orders uo
WHERE uo.order_status NOT IN ("future", "canceled")
AND uo.uid = 4172;


The data comes out fine, except the NULL fields should be
0
.

How can I return 0 for NULL in MySQL?

Answer

Use IFNULL:

IFNULL(expr1, 0)

From the documentation:

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.