0 votes
1 view
in SQL by (20.3k points)

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?

1 Answer

0 votes
by (40.4k points)

Try using IFNULL syntax like this:

IFNULL(expr1, 0)

For more information you can refer to this:

https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#function_ifnull

Note: If expr1 is not NULL, IFNULL() will return expr1; otherwise it'll return expr2. Generally, IFNULL() returns the numeric or string value, depending on the context in which it is used.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !

Categories

...