Sum causes one null data to appear

[newbie14]

Dear All,
I got a query as below.The problem is that even there is no data in that date range it generates one single null value row. The moment I remove the this line SUM(tblDepositFullRefund.amount) the problem is solved. Any solution to this?

SELECT CAST(CONCAT('DR',CONCAT(tblDepositFullRefund.depositFullRefundID)) AS CHAR) AS receiptID, 
SUM(tblDepositFullRefund.amount)
FROM tblDepositFullRefund 
WHERE tblDepositFullRefund.depositFullRefundDate BETWEEN 
'2011-12-20' AND '2011-12-21' AND tblDepositFullRefund.outletID=4 AND tblDepositFullRefund.amount>0 ORDER BY tblDepositFullRefund.depositFullRefundDate

[pritaeas]

No. SUM always returns a result, where NULL means no data to be summed.

[newbie14]

Dear Pritaeas,
So in that case I must check whether the sum is null or not is it in order to avoid the result? Am I right?

[pritaeas]

Yes, perhaps if you add a HAVING clause, you can avoid getting an result with NULL for SUM (didn't realize this before).

[newbie14]

Dear Pritaeas,
Where should I put the having clause ?

[pritaeas]

Something like this:

SELECT 
  CAST(CONCAT('DR',CONCAT(depositFullRefundID)) AS CHAR) AS receiptID, 
  SUM(amount) AS total
FROM tblDepositFullRefund 
WHERE depositFullRefundDate BETWEEN '2011-12-20' AND '2011-12-21' 
  AND outletID = 4 
  AND amount > 0 
ORDER BY depositFullRefundDate
HAVING total IS NOT NULL

[newbie14]

Dear Pritaeas,
Just to share with you the having total is not null must come before the order by else it gives syntax error. Thank you.