CODEWITHSUNDEEP
mysql
18Man
18Man

Reputation: 572

Where is my mistake selecting coalesce mysql?

I have a column discount_amount, if there's no SUM(discount_amount) (NULL) THEN it's zero (0).

I wrote the query with this

SELECT COALESCE(SUM(discount_amount) 
FROM order_discount
WHERE order_discount.discount_type_id = 6
  AND order_discount.order_match_id = om1.id, 0);

but I get an error

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM order_discount WHERE order_discount.discou' at line 2

Upvotes: 0

Views: 78

Answers (1)

GMB
GMB

Reputation: 222442

You seem to want:

SELECT COALESCE(SUM(discount_amount), 0)
FROM order_discount
WHERE order_discount.discount_type_id = 6 AND order_discount.order_match_id = om1.id;

This has to be part of bigger query, where derive table om1 is defined somehow.

Alternatively, you can COALESCE() the result of the subquery like this:

COALESCE(
    (
        SELECT SUM(discount_amount) 
        FROM order_discount
        WHERE order_discount.discount_type_id = 6 AND order_discount.order_match_id = om1.id
    ),
    0
)

Again, this only makes sense if included in a bigger query.

Upvotes: 1

Related Questions