tttpapi tttpapi - 13 days ago 6
MySQL Question

MySQL SUM returns no rows should return 0

There are no rows for this product_id. Result returns no rows instead of SUM = 0.

SELECT COALESCE(SUM(amount), 0) FROM store2product WHERE product_id = 6706434 GROUP BY product_id;


Is there a way to get result = 0?

Answer

There is no record for product_id = 6706434 in table store2product. As you group by product_id, you get one result row per product_id found with this query. As the product_id is not found, no row is returned.

Simple solution: remove GROUP BY.

SELECT 
  COALESCE(SUM(amount), 0) 
FROM store2product 
WHERE product_id = 6706434;

Now you get one result row in any case.