"Complicated" SQL query

[Chris]

Hello all-

Fairly new to SQL and I need to issue a pretty complex query (complex
being a relative term here :) ).

To dumb down my example for display purposes, I have two tables in my
schema

a Customer Table :

ID // unique primary key
Firstname
Lastname

and an Invoices table

ID // unique primary key
CustomerId // foreign key related to customer table
Total Price
There can be many Invoices per customer.

What I need to know, is how can I keep a running sum of 'Total Price'
for each invoice PER customer? My ultimate goal is to display the
results on a webpage in the following format :
CustomerName InvoiceNum TotalPrice
1. Joe Blow 1 10.00
2 15.00
3 20.00
-----
Total 45.00

2. Sally Fields 4 5.00
5 2.00
----
Total 7.00
....
....

I would prefer to tally the total using the mysql query, not to do a
running total as I display the results to the web page. I know
there's a GROUP BY clause that can be used in my query, but I'm not
quite sure how to apply it.

Any help would be greatly appreciated!

Sincerely,
Chris

[Isaac Blank]

SELECT concat(cus.firstname,' ',cus.lastname), inv.ID, inv.TotalPrice,
(SELECT SUM(TotalPrice) FROM Invoices inv2
WHERE inv2.CustomerID=cus.ID
AND inv2.ID <= inv.ID) runningTotal
FROM Customer cus,
Invoices inv
WHERE cus.ID=inv.CustomerID

"Chris" <c_*******@hotmail.com> wrote in message
news:cf**************************@posting.google.c om...

Hello all-

Fairly new to SQL and I need to issue a pretty complex query (complex
being a relative term here :) ).

To dumb down my example for display purposes, I have two tables in my
schema

a Customer Table :

ID // unique primary key
Firstname
Lastname

and an Invoices table

ID // unique primary key
CustomerId // foreign key related to customer table
Total Price
There can be many Invoices per customer.

What I need to know, is how can I keep a running sum of 'Total Price'
for each invoice PER customer? My ultimate goal is to display the
results on a webpage in the following format :
CustomerName InvoiceNum TotalPrice
1. Joe Blow 1 10.00
2 15.00
3 20.00
-----
Total 45.00

2. Sally Fields 4 5.00
5 2.00
----
Total 7.00
...
...

I would prefer to tally the total using the mysql query, not to do a
running total as I display the results to the web page. I know
there's a GROUP BY clause that can be used in my query, but I'm not
quite sure how to apply it.

Any help would be greatly appreciated!

Sincerely,
Chris

[Bill Karwin]

Chris wrote:

What I need to know, is how can I keep a running sum of 'Total Price'
for each invoice PER customer?

select c.id, sum(i.totalprice)
from customer c inner join invoices i on c.id = i.customerid

Then loop through each row returned from that query, and run this one
within the loop:

select i.invoicenum, i.totalprice
from invoices i
where i.customerid = ?

and pass the id from the first query as a parameter.

Regards,
Bill K.

[Chris]

Worked perfect!

Thanks for the help Bill!

Bill Karwin <bi**@karwin.com> wrote in message news:<ce*********@enews1.newsguy.com>...

Chris wrote:

What I need to know, is how can I keep a running sum of 'Total Price'
for each invoice PER customer?

select c.id, sum(i.totalprice)
from customer c inner join invoices i on c.id = i.customerid

Then loop through each row returned from that query, and run this one
within the loop:

select i.invoicenum, i.totalprice
from invoices i
where i.customerid = ?

and pass the id from the first query as a parameter.

Regards,
Bill K.