0

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
2
Contributors
6
Replies
7
Views
5 Years
Discussion Span
Last Post by newbie14
0

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?

0

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

0

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
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.