Return 0 if field is null in MySQL

user_295112 0
Viewed 293184

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?

mysql sql
5 Answers

None of the above answers were complete for me. If your field is named field, so the selector should be the following one:

IFNULL(`field`,0) AS field

For example in a SELECT query:

SELECT IFNULL(`field`,0) AS field, `otherfield` FROM `mytable`

Hope this can help someone to not waste time.

user_43618510
Related