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, 
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

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

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?

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

Dear Pritaeas,
Where should I put the having clause ?

Something like this:

  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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.