MySQL SUM returns no rows should return 0

Question

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?

Is amount definitely a numeric field? COALESCE will only return zero if SUM returns NULL, which presumably it is not. — BrynJ
– BrynJ, Commented Nov 25, 2016 at 10:10
Obviously you don’t get a record in the result set if there is no record that matches your WHERE clause. You'll need a JOIN or subquery of some sorts. — C3roe
– C3roe, Commented Nov 25, 2016 at 10:12
are you sure that the WHERE product_id = 6706434 return rows? — ScaisEdge
– ScaisEdge, Commented Nov 25, 2016 at 10:13

Thorsten Kettner · Accepted Answer · 2016-11-25 10:17:59Z

1

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.

answered Nov 25, 2016 at 10:17

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shakeer Mirza · Accepted Answer · 2016-11-25 10:18:28Z

0

Try this Trick..

Schema for your case

CREATE TABLE #TAB (ID INT, AMT DECIMAL(18,2))
INSERT INTO #TAB
SELECT 1,1200
UNION ALL
SELECT 1,120
UNION ALL 
SELECT 3, 100

Now query the table like

SELECT SUM(ISNULL(AMT,0)) AS AMT  FROM (

SELECT  ID, SUM(AMT)AMT FROM #TAB WHERE ID =2   GROUP BY ID
UNION ALL
SELECT NULL AS ID, NULL AS AMT

)A

answered Nov 25, 2016 at 10:18

Shakeer Mirza

5,1182 gold badges20 silver badges42 bronze badges

Collectives™ on Stack Overflow

MySQL SUM returns no rows should return 0

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related