How do I use SUM function in MySQL to return '0' if no values are found

Question

I want my sum function to return '0', if a value does not exist, Can anyone identify if there is a problem with the following SQL command. I have changed the order of the coalesce but this has not made a difference

SqlCommand scGetPostings = new SqlCommand(@"
SELECT 
  D1.dr, 
  D1.cr, 
  D1.asset_no, 
  (open_bal + dr - cr) AS closing_balance 
FROM (SELECT 
        COALESCE(SUM(dr_amount), 0) AS dr, 
        COALESCE(SUM(cr_amount), 0) AS cr, 
        asset_no 
      FROM posting, sysasset 
      WHERE posting.asset_no = @AssetNumber 
        AND period >= asset_open_per 
      GROUP BY asset_no) AS D1, asset 
WHERE D1.asset_no = asset.asset_no", DataAccess.AssetConnection);

Code looks like correct. What is the problem? Are you getting `NULL` for `dr` or `cr`? — Hamlet Hakobyan, Jan 23 '13 at 10:03
Yeah, if there is no value for this, i expect this code to return a '0' but its not returning anything, the code works because it returns records that have values in them. — CHRISTOPHER MCCONVILLE, Jan 23 '13 at 10:07
Do you want to see value or 0 if there are no records for each `asset_no`? — Hamlet Hakobyan, Jan 23 '13 at 10:10

Hamlet Hakobyan · Answer 1 · 2013-01-23T10:20:01.923

Try this:

SELECT 
  asset.asset_no, 
  COALESCE(dr, 0) AS dr, 
  COALESCE(cr, 0) AS cr, 
  (open_bal + COALESCE(dr, 0) - COALESCE(cr, 0)) AS closing_balance 
FROM 
    asset LEFT JOIN
     (SELECT 
        SUM(dr_amount) AS dr, 
        SUM(cr_amount) AS cr, 
        asset_no 
      FROM posting, sysasset 
      WHERE posting.asset_no = @AssetNumber 
        AND period >= asset_open_per 
      GROUP BY asset_no) AS D1 
ON D1.asset_no = asset.asset_no

score 0 · Answer 2 · answered Jan 23 '13 at 10:25

use if condition in your statement:

SqlCommand scGetPostings = new SqlCommand(@"
SELECT 
  D1.dr, 
  D1.cr, 
  D1.asset_no, 
  (open_bal + dr - cr) AS closing_balance 
FROM (SELECT 
        SUM(if(dr_amount is null , 0 , dr_amount )) AS dr, 
        SUM(if(cr_amount is null , 0 , cr_amount )) AS cr, 
        asset_no 
      FROM posting, sysasset 
      WHERE posting.asset_no = @AssetNumber 
        AND period >= asset_open_per 
      GROUP BY asset_no) AS D1, asset 
WHERE D1.asset_no = asset.asset_no", DataAccess.AssetConnection);

How do I use SUM function in MySQL to return '0' if no values are found

2 Answers2