Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
end 
from Invoice    
share|improve this question
    
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
    
Voting to be migrated to SO. Looks simple question, yet not clearly explained. Moreover, has been crossposted at SO: stackoverflow.com/questions/17500904/… –  ypercube Jul 6 '13 at 14:30
    
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
add comment

closed as off-topic by ypercube, Paul White, Jon Seigel, Mark Storey-Smith, jcolebrand Jul 7 '13 at 1:56

If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
    
Thank you @Travis Gan. Really it was as straight forward as it looks. I'd just lost my mind and thought that it might be done by 'Case' statement and now i am laughing at my self. Thank you once again! Cheers! :) –  sohaiby Jul 7 '13 at 9:27
    
Sorry everyone. I wanted this answer quickly that's why I posted it here as well as on SO. Didn't know i can't post questions of such type here or this is illegal or unethical to post same question on different sites. Sorry all, my bad!! :/ –  sohaiby Jul 7 '13 at 9:29
add comment

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