I am having a probelm. I have a table of Invoice with different Invoices. Each Invoice has a unique Invoice Number and some amount. Invoices are of two type. One is of Invoice Type, and the other is of Settled type. If there's a invoice created, the entry in the table will be of invoice type, the amount will be in positive. If the user paid that invoice, another entry with the same invoice number will be entered in the table but with the negative amount because the user has paid that amount.
Now i want to to show the record if there exist any two records with same invoice number, then show the record with calculating the sum of the Amount (which means calculating the Invoice type record and the settlement type record). And those records which have a unique invoice number (with no settlement record) should be displayed as it is (without sum). Help? I guess the query will be some what of this kind.., but that's not exactly what i want!

    Select InvoiceNumber, Amount = case when COUNT(InvoiceNumber) > 1 
then SUM(Amount) 
else Amount 
from Invoice    
  • If there's only one line for an invoice number, sum(amount) == amount, so always using sum should just work. I'm not sure I see an actual problem here. – Mat Jul 6 '13 at 11:14
  • This question appears to be off-topic because it is about the same topic as stackoverflow.com/questions/17500904/… – jcolebrand Jul 7 '13 at 1:56

If I understand correctly, your only concern is about the balance of each invoice number. Can't a SUM grouping by the invoice number give you what you need?

Here is the Sqlfiddle

SELECT InvoiceNumber, SUM(Amount) AS Amount
FROM Invoice
GROUP BY InvoiceNumber
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.