I've been asked some questions, so here is some add'l info:
Here is a snapshot of the order contents: mysql> SELECT * FROM OrderContents WHERE OrderID = 3116; +-----------+---------+-------------+---------------+-----+----------+------ ---------------+ | ContentID | OrderID | InventoryID | Price | Qty | QtyAvail | ShipDate | +-----------+---------+-------------+---------------+-----+----------+------ ---------------+ | 2425 | 3116 | 317 | 19.5000000000 | 1 | 1 | 2004-09-21 13:44:34 | | 2426 | 3116 | 347 | 19.5000000000 | 1 | 1 | 2004-09-21 13:44:34 | | 2427 | 3116 | 367 | 19.5000000000 | 1 | 1 | 2004-09-21 13:44:34 | | 2428 | 3116 | 587 | 31.5000000000 | 1 | 1 | 2004-09-21 13:44:34 | | 2429 | 3116 | 627 | 31.5000000000 | 1 | 1 | 2004-09-21 13:44:34 | | 2430 | 3116 | 923 | 38.5000000000 | 1 | 1 | 2004-10-05 12:30:43 | | 2431 | 3116 | 1199 | 42.5000000000 | 1 | 1 | 2004-09-30 10:28:18 | | 2432 | 3116 | 1236 | 44.5000000000 | 1 | 1 | 2004-10-05 12:30:43 | | 2433 | 3116 | 1263 | 44.5000000000 | 1 | 1 | 2004-09-21 13:44:34 | | 2434 | 3116 | 1492 | 46.5000000000 | 1 | 1 | 2004-10-05 12:30:43 | | 2435 | 3116 | 1505 | 38.5000000000 | 1 | 1 | 2004-10-05 12:30:43 | +-----------+---------+-------------+---------------+-----+----------+------ ---------------+ Here is a snapshot of the charge details: mysql> SELECT ANID, PaymentID, OrderID, ApprovalCode, Amount, mysql> TransactionType FROM AuthorizeNet WHERE OrderID = 3116; +------+-----------+---------+--------------+--------+--------------------+ | ANID | PaymentID | OrderID | ApprovalCode | Amount | TransactionType | +------+-----------+---------+--------------+--------+--------------------+ | 2188 | 660 | 3116 | 165627 | 183.00 | prior_auth_capture | | 2832 | 660 | 3116 | 127447 | 42.50 | auth_capture | | 3379 | 660 | 3116 | 164026 | 168.00 | auth_capture | +------+-----------+---------+--------------+--------+--------------------+ 6 rows in set (0.01 sec) I'm trying to get the two total amounts by OrderID in 1 Query... I'm using MySQL 4.1.4 Best Regards, Andrew Kuebler AK Communications, Inc. Phone: (866) 925-2666 x123 / Fax: 866-925-3296 (Toll-free) <http://www.akcomm.com> http://www.akcomm.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:58 AM To: Andrew Kuebler Cc: [EMAIL PROTECTED] Subject: RE: SELECT SUM + 2 JOINS OK, that makes better sense. Please respond to the list (you should always try keep any list responses CC:ed to the list so that everyone else can help and learn from the exchange, too) with the information that Rhino requested and I am sure someone will be able to help you work out your query. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Andrew Kuebler" <[EMAIL PROTECTED]> wrote on 10/13/2004 11:53:27 AM: > Sorry, I said it wrong. I want the total purchased in an order and > the total charged for that order. I am only looking for totals at an > order level, not by customer. > Best Regards, > Andrew Kuebler > AK Communications, Inc. > Phone: (866) 925-2666 x123 / Fax: 866-925-3296 (Toll-free) > http://www.akcomm.com > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 13, 2004 11:37 AM > To: Andrew Kuebler > Cc: [EMAIL PROTECTED] > Subject: Re: SELECT SUM + 2 JOINS > > > I believe what you want needs two queries, one to total up on the > order# the other to total up by customer. I wouldn't recommend > trying to do it all at once as they are two different (but similarly > asked) questions. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > "Andrew Kuebler" <[EMAIL PROTECTED]> wrote on 10/13/2004 11:22:37 AM: > > > I have a MySQL table with Order#'s (Primary Key). > > > > > > > > I want to total the number of items per order which I do through an inner > > join to the order contents, but then I want to also get a total on the > > amounts a customer has been billed to make sure there isn't a customer that > > has been shipped something they haven't paid for. Everything is ok until I > > do the left join on the payments which there could be more than one of for > > each order. Is it possible to do what I am asking in one query? > > > > > > > > I hope I have made sense. > > > > Best Regards, > > > > Andrew > > > > > >
