I have a query type:

SELECT COUNT(*) FROM (SELECT vendors_id, merchants_id, SUM(amount) AS amount, SUM(commission_amount) AS commission_amount FROM (SELECT vendors_id, merchants_id, amount, commission_amount FROM (SELECT vendors.id AS vendors_id, merchants_id, SUM(transactions_cash.amount) AS amount, SUM( transactions_cash.commission_amount ) AS commission_amount FROM ibaserver.transactions_cash, ibaserver.vendors, ibaserver.merchants WHERE transactions_cash.vendors_id = vendors.id AND TIME > 1466680920208 AND TIME <= 1466681880067 AND merchants_id = merchants.id GROUP BY transactions_cash.merchants_id ORDER BY transactions_cash.merchants_id) a UNION ALL SELECT vendors_id, merchants_id, amount, commission_amount FROM (SELECT vendors.id AS vendors_id, merchants_id, SUM( transactions_cash_archive.amount ) AS amount, SUM( transactions_cash_archive.commission_amount ) AS commission_amount FROM ibaserver.transactions_cash_archive, ibaserver.vendors, ibaserver.merchants WHERE transactions_cash_archive.vendors_id = vendors.id AND TIME > 1466680920208 AND TIME <= 1466681880067 AND merchants_id = merchants.id GROUP BY transactions_cash_archive.merchants_id ORDER BY transactions_cash_archive.merchants_id) b) s) q

when internal request

  SELECT vendors_id, name, SUM(amount) AS amount FROM ....

returns data, the result is returned successfully. If the inner query returns all NULL :

internal query result

then the entire request returns an error:

 Error Code: 1048 Column 'vendors_id' cannot be null

How to make so that in a case when the internal request returns all NULL , the result was 0?

  • Please provide a full request. - cheops
  • Edited a post. - Ksenia

1 answer 1

To do this, you can use the standard COALESCE() function, which takes a list of parameters and returns the first non-NULL value.

 SELECT COALESCE(vendors_id, 0), name, COALESCE(SUM(amount), 0) AS amount FROM ....

If vendors_id returns a non-NULL value, the function will return it, otherwise it will go to the second argument and return 0.