Revisions to How get the sum of two tables value using inner join

i changed the GROUP BY clause at Return Cheque table...

Source Link

edit approved Jun 24, 2015 at 21:39

M.I.M.F

329
1
7
19

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName,
    i.InvoiceTotalDue, i.InvoiceTotalBalance,
    r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance, (i.InvoiceTotalBalance + r.ReturnChequeTotalBalance) AS TotalBalance
FROM Client_TBL c
LEFT JOIN ( 
SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue,
            SUM(TotalBalance) AS InvoiceTotalBalance
        FROM Invoice_TBL
        GROUP BY ClientNo 
) AS i
    ON c.ClientNo = i.ClientNo
LEFT JOIN ( 
SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue,
            SUM(TotalBalance) AS ReturnChequeTotalBalance
        FROM ReturnCheque_TBL)
GROUP ASBY rClientNo
 ) AS r ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName,
    i.InvoiceTotalDue, i.InvoiceTotalBalance,
    r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance
FROM Client_TBL c
LEFT JOIN (SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue,
            SUM(TotalBalance) AS InvoiceTotalBalance
        FROM Invoice_TBL
        GROUP BY ClientNo) AS i
    ON c.ClientNo = i.ClientNo
LEFT JOIN (SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue,
            SUM(TotalBalance) AS ReturnChequeTotalBalance
        FROM ReturnCheque_TBL) AS r
    ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName, i.InvoiceTotalDue, i.InvoiceTotalBalance, r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance, (i.InvoiceTotalBalance + r.ReturnChequeTotalBalance) AS TotalBalance
FROM Client_TBL c
LEFT JOIN ( 
SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue, SUM(TotalBalance) AS InvoiceTotalBalance
FROM Invoice_TBL
GROUP BY ClientNo 
) AS i ON c.ClientNo = i.ClientNo
LEFT JOIN ( 
SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue, SUM(TotalBalance) AS ReturnChequeTotalBalance
FROM ReturnCheque_TBL
GROUP BY ClientNo
) AS r ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

I changed the JOINS to LEFT JOINS to avoid dropping client who had no return checks.

Source Link

edit approved Jun 24, 2015 at 21:00

Chris

2.4k
16
20

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName,
    i.InvoiceTotalDue, i.InvoiceTotalBalance,
    r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance
FROM Client_TBL c
LEFT JOIN (SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue,
            SUM(TotalBalance) AS InvoiceTotalBalance
        FROM Invoice_TBL
        GROUP BY ClientNo) AS i
    ON c.ClientNo = i.ClientNo
LEFT JOIN (SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue,
            SUM(TotalBalance) AS ReturnChequeTotalBalance
        FROM ReturnCheque_TBL) AS r
    ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName,
    i.InvoiceTotalDue, i.InvoiceTotalBalance,
    r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance
FROM Client_TBL c
JOIN (SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue,
            SUM(TotalBalance) AS InvoiceTotalBalance
        FROM Invoice_TBL
        GROUP BY ClientNo) AS i
    ON c.ClientNo = i.ClientNo
JOIN (SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue,
            SUM(TotalBalance) AS ReturnChequeTotalBalance
        FROM ReturnCheque_TBL) AS r
    ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName,
    i.InvoiceTotalDue, i.InvoiceTotalBalance,
    r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance
FROM Client_TBL c
LEFT JOIN (SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue,
            SUM(TotalBalance) AS InvoiceTotalBalance
        FROM Invoice_TBL
        GROUP BY ClientNo) AS i
    ON c.ClientNo = i.ClientNo
LEFT JOIN (SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue,
            SUM(TotalBalance) AS ReturnChequeTotalBalance
        FROM ReturnCheque_TBL) AS r
    ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

Source Link

created Jun 24, 2015 at 20:17

Kenneth Fisher

24.3k
12
61
115

This may not be the best way to do this but it will work.

SELECT c.ClientNo, c.ClientId, c.FullName,
    i.InvoiceTotalDue, i.InvoiceTotalBalance,
    r.ReturnChequeTotalDue, r.ReturnChequeTotalBalance
FROM Client_TBL c
JOIN (SELECT ClientNo, SUM(TotalDue) AS InvoiceTotalDue,
            SUM(TotalBalance) AS InvoiceTotalBalance
        FROM Invoice_TBL
        GROUP BY ClientNo) AS i
    ON c.ClientNo = i.ClientNo
JOIN (SELECT ClientNo, SUM(TotalDue) AS ReturnChequeTotalDue,
            SUM(TotalBalance) AS ReturnChequeTotalBalance
        FROM ReturnCheque_TBL) AS r
    ON c.ClientNo = r.ClientNo

And of course you could change the subqueries into CTEs. But basically you are going to collect the data pre join since otherwise you are probably going to get bad data (Client 2 for example will have Invoice info that is twice as large and ReturnCheque that is 3 times as large.)

Stack Exchange Network

Return to Answer