I'm having problem with SUM() in MySQL

Question

Hi All,

I want to fetch the BalanceAmt of a particular Customer attended by a particular attender. The criteria is that a customer may buy more than one items. Each bought items has been recorded individually. At some cases 'attender' value would be NULL.

Here's my query:

SELECT Customer, AttendedBy, SUM(BalanceAmt) FROM billing GROUP BY Customer 
     HAVING AttendedBy='attender' AND Customer='CustomerName'

My problem is I'm getting the value summed up not attended by the 'attender' (i.e., including NULL value). Help me to do it..

Thanx in advance..

Answer 1

I think you need to include a where clause to exclude your null values... Something like the following should do the trick:

SELECT Customer, AssignedTo, SUM(BalanceAmt) FROM billing 
WHERE not(BalanceAmt is null) AND AttendedBy='attender' AND Customer='CustomerName'
GROUP BY Customer, AssignedTo

HTH.

Answer 2

I have the feeling haven't gotten the question right, but I would write the SELECT Statement like this

SELECT Customer, SUM(BalanceAmt) 
FROM billing 
WHERE AttendedBy='attender' AND Customer='CustomerName' 
GROUP BY Customer  

I am not sure what the AssignedTo is. If you want the sum on Customer, AssignedTo then it would be:

SELECT Customer, AssignedTo, SUM(BalanceAmt) 
FROM billing 
WHERE AttendedBy='attender' AND Customer='CustomerName' 
GROUP BY Customer, AssignedTo