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

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:

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

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.