I'm having problem with SUM() in MySQL

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'
开发者_JAVA技巧

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

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 

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.

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

In your above query group by is not required as you have put the column in the where clause.

You can remove it from select and you remove group by as well.

You can write something like this, to get the Customer-wise sum.

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