Solved

Total amounts from two different tables.

Posted on 2006-06-29
7
301 Views
Last Modified: 2008-02-01
I have three tables as shown below.  Table A is a list of shipments.  Table B is a list of Charges for each shipment. Table C is a list of charges on each invoice.

Table A (ShipID is a auto increment)
ShipID  Recipient
 1         Mary Boyd
 2         Tom Delay
 3         George Bush

Table B  (ChargeID is a auto increment, ShipID is a foreign key)
ChargeID   ShipID  ChargeType  ChargeAmount
  1              1         BaseCharge    6.00
  2              1         Fuel               6.00
  3              2         BaseCharge    12.50
  4              2         Fuel               3.00
  5              3         CustomsFees  55.00

Table C (ChargeID is a foreign key)
ChargeID  Amount   Invoice#
  1             6.00      55245
  2              5.00       55245
  3              9.00       55246
  3              3.50       55247
  4              3.00       55246
  5             49.00      55248

There can be multiple records in Table C for each record in Table B and there can be multiple records in Table B for each in Table A.
I need a sql statement that can display the following information.

ShipID  TotalChargedAmount   TotalInvoiceAmount    Balance
  1                12.00                          11.00                  1.00
  2                15.50                          15.50                  0.00
  3                55.00                          49.00                  6.00

I have one statement but it take a really long time to calculate.  I need a fast sql statement.  The following statement takes about 25 seconds.  I need a very quick statement (under 1 sec)  I have about 10000 records in each table.

select TableA.ShipID, sum(TotalBilledSQL.TotalBilled) AS `AmountPaid`,sum(TotalChargeSQL.TotalCharges) AS `OrigAmount`
,(sum(TotalChargeSQL.TotalCharges)- sum(TotalBilledSQL.TotalBilled)) AS `Balance` from ((TableA left join (SELECT TableB.ShipID, sum(TableB.ChargeAmount) AS TotalCharges FROM TableB GROUP BY TableB.ShipID) AS TotalChargeSQL on((TableA.ShipID = TotalChargeSQL.ShipID))) left join (SELECT TableB.ShipID, sum(TableC.Amount) as TotalBilled FROM TableC RIGHT JOIN TableB ON TableB.ChargeID = TableC.ChargeID GROUP BY TableB.shipid) as TotalBilledSQL  on((TableA.shipid = TotalBilledSQL.ShipID))) group by TableA.ShipID
0
Comment
Question by:dyzenment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 17015197
What are your definitions for TotalChargedAmount, TotalInvoiceAmount, Balance?

If Total Charged is the sum of all charges, Total Invoice is the sum of all invoiced amounts - and Balance is the difference between the two

*********************************************
Select
      Summary.ShipId,
      Summary.TotalChargedAmount,
      Summary.TotalBilledAmount,
      (Summary.TotalChargedAmount -      Summary.TotalBilledAmount) Balance
From
      (
      Select
            A.ShipId,
            (Select sum(B.ChargeAmount) from Table_B B where B.ShipID = A.ShipID) TotalChargedAmount,
            (Select sum(C.Amount) from Table_C C, Table_B B where C.Charge_ID = B.ChargeID and B.ShipID = A.ShipID) TotalBilledAmount
      From
            Table_A A
      ) Summary
*********************************************
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17015205
It may not just be the query - you might need to improve your indexes; configuration and may need to specifically reference the indexes in the query for optimum performance.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 17017875
I think it would make sense to add 'totalcharged', 'totalinvoiced', and 'balance' columns to table A.  If that's not possible, I would do it with a temporary table.  I have an irrational dislike for subselects.

What types are you using for your monetary values?  Decimal(12,2) or something similar, right?  If you use doubles you can have subtle problems with inequality and rounding.

CREATE TEMPORARY TABLE balances (shipID int primary key, totalcharged decimal(12,2) not null default 0, totalinvoiced decimal(12,2) not null default 0, balance decimal(12,2) not null default 0);

LOCK TABLES A READ, B READ, C READ, balances WRITE;
INSERT INTO balances (shipID) select shipID from A;
UPDATE balances, B SET totalcharged = totalcharged + ChargeAmount WHERE balances.shipID = B.shipID;
UPDATE balances, B, C SET totalinvoiced = totalinvoiced + C.Amount WHERE balances.shipID = B.shipID and B.invoiceID = C.invoiceID;
UNLOCK TABLES;

-- now all the information you need is in the balances temporary table
SELECT shipID, totalcharged, totalinvoiced, (totalcharged - totalinvoiced) AS balance FROM balances;
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 35

Expert Comment

by:Raynard7
ID: 17021910
I am inclined to disagree with Nova - depending on how often a table is referenced then I would not always make temporary tables.  I find that as they write to disk and need cleaning up - they can take quite some time and are unnecessary.

SubSelects are in the ANSI standard and MySql handles them well.  A sub select is where you do sub queries and then reference them in an outer query - the reason I wrote the above query the way I did is so you are only totaling each piece of data once - then working with the sub totals.

Another option that you could use to increase the speed is to create a view - that does what Nova's temporary table does - which is in effect a compiled query; this would run faster than a temporary table as it is already memory resident and would give you the same result.

0
 
LVL 32

Expert Comment

by:awking00
ID: 17031047
select x.shipid, x.chg_amt, y.inv_amt, (x.chg_amt - y.inv_amt) balance
from
(select shipid, sum(chargeamount) chg_amt
from tableb
group by shipid) x
,(select c.shipid shipid, sum(i.amount) inv_amt
  from tableb b
      ,tablec c
  where b.chargeid = c.chargeid
  group by b.shipid) y
where x.shipid = y.shipid
;
0
 
LVL 32

Expert Comment

by:awking00
ID: 17031059
Sorry, I used different aliases in my test..

select x.shipid, x.chg_amt, y.inv_amt, (x.chg_amt - y.inv_amt) balance
from
(select shipid, sum(chargeamount) chg_amt
from tableb
group by shipid) x
,(select b.shipid shipid, sum(c.amount) inv_amt
  from tableb b
      ,tablec c
  where b.chargeid = c.chargeid
  group by b.shipid) y
where x.shipid = y.shipid
;
0
 
LVL 32

Expert Comment

by:awking00
ID: 17032880
Didn't see any reason to join tablea since it didn't appear you were trying to get the recipient name.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question