SQL Query Question

[fujin]

In my table, I have records that contain Customer, Invoice, Total ... Initially, the records will be something like this:

Customer Invoice Total

Customer A Invoice 1 10
Customer A Invoice 1 20
Customer A Invoice 1 30 = 60

Customer B Invocie 2 15
Customer B Invocie 2 25
Customer B Invocie 2 30 = 70

Customer A Invoice 3 20
Customer A Invoice 3 20
Customer A Invoice 3 20 = 60

Customer B Invoice 4 10
Customer B Invoice 4 10 = 20

What i want for my query is to display these

Customer Sub Total
Customer A 120
Customer B 90

Sub Total, is the one that i do not know abt.. totalling up all the totals, based on diff customer.. any idea?

thanks..

[John McKernan]

Just off the top of my head (I haven't tested it):

Select CustomerName, Sum(InvoiceAmt) As SubTotal From Customers Group By CustomerName

[fujin]

Thanks a lot! Appreciate it..

[Wokawidget]

Assuming that you would also like to know the total for each customer, per invoice then use:

SELECT CustomerName, InvoiceNumber, SUM(InvoiceAmt) AS SubTotal 
FROM Customers
GROUP BY CustomerName, InvoiceNumber
ORDER BY CustomerName, InvoiceNumber

If you want to see which Companies have had order worth greater than say £100 then use the following, which is the same as above, but one added line:

SELECT CustomerName, InvoiceNumber, SUM(InvoiceAmt) AS SubTotal 
FROM Customers
GROUP BY CustomerName, InvoiceNumber
HAVING SUM(InvoiceAmt) > 100
ORDER BY CustomerName, InvoiceNumber

Thought you might be interested in that...

Hope it helps,

Woka