SQL SUM on multiple INNER JOIN

I am trying to get the sum of particular fields through multiple table using joins. I am getting the SUM wrong when I I try to get the values together.

I have table State as

| STATE |   MONTH | ID | TOTAL |
--------------------------------
|    FL |    July |  1 | 10000 |
|    CA |   April | 32 |  2000 |

I have another table Balance as

| STATE |  Balance|
-------------------
|    FL |      100|
|    FL |      200|
|    CA |      300|
|    CA |      200|
|    CA |      100|

I have one more table Loan as

| STATE |     Loan|
-------------------
|    FL |      500|
|    FL |      600|
|    CA |      700|
|    CA |      100|
|    CA |      200|

What I want as a result from my query is,

| STATE |     Loan| Balance|
----------------------------
|    FL |     1100|     300|
|    CA |     1000|     600|

When I try to use the following query I am getting the sum for loan with state correctly,

SELECT 
  S.STATE, 
  SUM(L.Loan) 
FROM State AS S
  INNER JOIN Loan AS L ON L.STATE = S.STATE
GROUP BY
 S.STATE

I get the following result,

| STATE |      Loan|
--------------------
|    FL |      1100|
|    CA |      1000|

Similarly I can get the sum from the Balance Table. What I am trying to achieve is to get the sum form both the tables using a single transaction.

Although, I am able to get the desired values, if I do something like this,

SELECT
  STATE AS STATE
  SUM(DataSetB.Balance) AS Balance
FROM
    (
    SELECT 
      STATE AS STATE,
      B.Balance AS Balance
    FROM
        (
        SELECT 
          S.STATE AS STATE, 
          SUM(L.Loan) AS Loan,
        FROM State AS S
          INNER JOIN Loan AS L ON L.STATE = S.STATE
        GROUP BY S.STATE
        )
        AS DataSetL
     INNER JOIN Balance AS B ON B.STATE = DataSetL.STATE
     GROUP BY
       DataSetL.STATE, B.Balance
     ) AS DataSetB
GROUP BY 
  DataSetB.STATE

However this is not very feasible on the large data set that I have. I tried,

SELECT 
  S.STATE AS STATE, 
  SUM(L.Loan) AS Loan,
  SUM(B.Balance) AS Balance
FROM State AS S
  INNER JOIN Loan AS L ON L.STATE = S.STATE
  INNER JOIN Balance AS B ON B.STATE = S.STATE
GROUP BY 
  S.STATE

But this gives me values as multiple of the actual value. It is actually the multiple of number of rows present in the child tables.

I am not asking for an exact solution, but any kind of pointers would be nice.

Answer

Do the aggregation for each table in a separate sub-query, then join:

SELECT 
  S.STATE AS STATE, 
  L.Loan,
  B.Balance
FROM State AS S
  LEFT JOIN (select state, sum(loan) as Loan
              from loan
              group by state) AS L ON L.STATE = S.STATE
  LEFT JOIN (select state, sum(Balance) as balance
              from balance
              group by state) AS B ON B.STATE = S.STATE

(LEFT JOINs just in case a state has no loan or balance rows.)

Advertisement

Answer