SQL sum-function and count-function over several tables result in multiplied values

Question

After driving myself crazy by countless attempts, I turn to you and hope someone is willing to help me....

I have 4 tables: SO (ServiceOrder), PR (PreRecording), IH (InvoiceHeader) and IL (InvoiceLine), with the columns and rows like underneath:

SO (ServiceOrder):

SO.No_
SO2101234
SO2101235
SO2101237
SO2101239

PR (PreRecording):

PR.No_	PR.Line	PR.Amount
SO2101234	1	50
SO2101234	2	18
SO2101239	1	110
SO2101239	2	40
SO2101239	3	40

IH (InvoiceHeader):

IH.No_	IH.SO
VF2134889	SO2101234
VF2134890	SO2101235
VF2134891	SO2101239
VF2134892	SO2101234
VF2134893	SO2101239

IL (InvoiceLine):

IL.DocNo_	IL.LineNo	IL.LineAmount
VF2134889	1	55
VF2134889	2	23
VF2134890	1	12
VF2134890	2	34
VF2134890	3	87
VF2134891	1	114
VF2134892	1	53
VF2134892	2	25
VF2134892	3	17
VF2134892	4	15
VF2134893	1	43
VF2134893	2	38

Relations:

• PR.No_ = SO.No_
• IH.SO = SO.No_
• IL.DocNo_ = IH.No_

Desired outcome:

SO.No_	PR.SumAmount	IH.SumAmount
SO2101234	68	188
SO2101235	0	133
SO2101237	0	0
SO2101239	190	195

With my usual simple inner/outer/joins I don't succeed, it's clear that some SELECT's should be nested. With a simple query to combine SO and PR, I have no problem. With a simple query to combine SO and IH, I have no problem. However, when I try to setup a query to get all of this in one output, it gets messed up and values start to multiply due to the rows that are used/found in the several tables.

Many thanks in advance....

Answer 1

You can simply nest your queries so that you do the outer SELECT from a flat inner SELECT. This will also work if your RDBMS version does not support WITH CTEs.

To preserve the rows with NULL values, I used LEFT JOINS and additionally the COALESCE function to get 0 as result instead of NULL, as in your desired outcome.

   SELECT PRGRP.No_, 
          COALESCE(PRGRP.SumAmount, 0) AS PRAmount,
          COALESCE(SUM(IL.LineAmount), 0) AS ILAmount
     FROM (   SELECT SO.No_, SUM(PR.Amount) AS SumAmount
                FROM ServiceOrder SO 
           LEFT JOIN PreRecording PR 
                  ON PR.No_ = SO.No_
            GROUP BY SO.No_
          ) PRGRP
LEFT JOIN InvoiceHeader IH 
       ON IH.SO = PRGRP.No_
LEFT JOIN InvoiceLine IL 
       ON IL.DocNo_ = IH.No_
 GROUP BY PRGRP.No_

Result:

No_	PRAmount	ILAmount
SO2101234	68	188
SO2101235	0	133
SO2101237	0	0
SO2101239	190	195

Full MySQL example in this db<>fiddle.