SQL Server – How to Sum Values from Two Tables Using Inner Join

sql-server-2008-r2

There are there tables the below image.

enter image description here

My question is retrieve from each customers and get the sum of Invoice total due, Invoice Total balance in invoice table and return cheque total due and return cheque total balance in return cheque table…

I wanted the answer like below image from the above 3 table using inner join

Please help me thank you.

enter image description here

Best Answer

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.)

Related Solutions

Sql-server – MS Sql Server Management Studio gives error on start

Error code 3417 means that you have problems with your master database. More detailed information on this error can be found here: (http://msdn.microsoft.com/en-us/library/aa337262%28v=sql.105%29.aspx). The list of SQL server errors can be found in the MSDN by following address: http://msdn.microsoft.com/en-us/library/cc645601(v=sql.105).aspx. The solution is to recover the master database from the last backup. After this the SQL server will start and you will be able to connect.

SQL Server – Query Optimization for Subquery Using Inner Join

You could rewrite with a LEFT JOIN:

SELECT
  IT.[iInternalTransactionID]
, IT.[iInternalAccountID]
, C.[iContractID]
, IT.[dtTransactionDate] AS TransactionDate
, (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName
...
-- ### unchanged up to here ##--
, G.TermNo
, G.TermNo * G.MaxMonthlyAmount AS TotalAmount
-- ### end ### --

FROM 
    [tbl_InternalTransactions] IT
    INNER JOIN [tbl_Contract] C ON C.[iContractID] = IT.[iContractID]
    INNER JOIN [tbl_Client] CL ON CL.[iClientID] = C.[iClientID]
    INNER JOIN [tbl_FinanceStructure] FS ON FS.[iFinanceStructureID] = C.[iFinanceStructureID]
    INNER JOIN [tbl_InternalAccount] IA ON IT.[iInternalAccountID] = IA.[iInternalAccountID]
    AND IA.[iInternalAccountID] = @InternalAccountID

--  ### the two subqueries converted to a LEFT JOIN ###
    LEFT JOIN
      ( SELECT CPH.iContractID
             , CPH.dtTransactionDate
             , MAX(CPH.iTermNo) AS TermNo
             , MAX(CPH.cMonthlyAmount) AS MaxMonthlyAmount
        FROM tbl_ContractPaymentHistory CPH
        WHERE CPH.iInternalAccountID = @InternalAccountID
        GROUP BY CPH.iContractID
               , CPH.dtTransactionDate
      ) G
      ON  G.iContractID = C.iContractID
      AND G.dtTransactionDate = IT.dtTransactionDate
-- ### end of changes ###

WHERE 
    IT.[dtTransactionDate] >= @FromDate
    AND IT.[dtTransactionDate] <= @ToDate
...

or using OUTER APPLY:

--  ### the two subqueries converted to an OUTER APPLY ###
    OUTER APPLY
      ( SELECT MAX(CPH.iTermNo) AS TermNo
             , MAX(CPH.cMonthlyAmount) AS MaxMonthlyAmount
        FROM tbl_ContractPaymentHistory CPH
        WHERE CPH.iContractID = C.iContractID
          AND CPH.iInternalAccountID = @InternalAccountID
          AND CPH.dtTransactionDate = IT.dtTransactionDate
      ) G
-- ### end of changes ###

These are just rewritings along the lines you tried. Not at all sure if they improves efficiency. They will probably do but the main bottleneck may be elsewhere (indexes).

Best Answer

Related Solutions

Sql-server – MS Sql Server Management Studio gives error on start

SQL Server – Query Optimization for Subquery Using Inner Join

Related Question