title: SQL SUM on multiple INNER JOIN
body: <p>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.</p>
  
  <p>I have table State as </p>
  
  <pre><code>| STATE |   MONTH | ID | TOTAL |
  --------------------------------
  |    FL |    July |  1 | 10000 |
  |    CA |   April | 32 |  2000 |
  </code></pre>
  
  <p>I have another table Balance as</p>
  
  <pre><code>| STATE |  Balance|
  -------------------
  |    FL |      100|
  |    FL |      200|
  |    CA |      300|
  |    CA |      200|
  |    CA |      100|
  </code></pre>
  
  <p>I have one more table Loan as</p>
  
  <pre><code>| STATE |     Loan|
  -------------------
  |    FL |      500|
  |    FL |      600|
  |    CA |      700|
  |    CA |      100|
  |    CA |      200|
  </code></pre>
  
  <p>What I want as a result from my query is,</p>
  
  <pre><code>| STATE |     Loan| Balance|
  ----------------------------
  |    FL |     1100|     300|
  |    CA |     1000|     600|
  </code></pre>
  
  <p>When I try to use the following query I am getting the sum for loan with state correctly,</p>
  
  <pre><code>SELECT 
    S.STATE, 
    SUM(L.Loan) 
  FROM State AS S
    INNER JOIN Loan AS L ON L.STATE = S.STATE
  GROUP BY
   S.STATE
  </code></pre>
  
  <p>I get the following result,</p>
  
  <pre><code>| STATE |      Loan|
  --------------------
  |    FL |      1100|
  |    CA |      1000|
  </code></pre>
  
  <p>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. </p>
  
  <p>Although, I am able to get the desired values, if I do something like this,</p>
  
  <pre><code>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
  </code></pre>
  
  <p>However this is not very feasible on the large data set that I have.
  I tried,</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I am not asking for an exact solution, but any kind of pointers would be nice.</p>