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 3 6149
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
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Alan McIntyre |
last post by:
Hi all,
I have a list of items that has contiguous repetitions of values, but
the number and location of the repetitions is not important, so I just
need to strip them out. For example, if my...
|
by: Chris |
last post by:
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...
|
by: Jay Chan |
last post by:
I keep getting the following error message when I run a serie of SQL
commands:
Server: Msg 9002, Level 17, State 6, Line 15
The log file for database 'tempdb' is full.
Back up the transaction...
|
by: dover |
last post by:
For the code, outputfile << *p << endl;
Someone suggests: Don't use endl here; it is flushing output stream every
time. Use plain '\n'.
What's the difference of using "endl" and "\n" above?...
|
by: Martien van Wanrooij |
last post by:
I would like to retrieve, let us say, the First Monday after a certain date,
so my (imaginary) function
could be something like
echo weekdayAfter("28 July 2005", "Monday") should return "1 August...
|
by: usenet |
last post by:
I have inherited a table where date information was saved from PHP as a VARCHAR.
(Sigh.) This means that the so-called date fields look like this :
August 1, 2005, 9:09 am EDT
October 13, 2004,...
|
by: Niranjan |
last post by:
I have a relatively straight forward report. The query that populates
data for this report is complicated. The report opens without any
problem and I can print it as well.
However, If I have...
|
by: Dave Smithz |
last post by:
Hi there,
Been working on an evolving DB program for a while now. Suddenly I have come
across a situation where I need to update a table based on a group by query.
For example, I have a table...
|
by: M G Henry |
last post by:
I have a tabbed form that contains 12 different "pages" and when I try
and run the form I get the error message too many fields defined ---
which I believe is the 255 field limit in the record...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |