title: 3 Table Join with SUM and GROUP BY not working
body: <p>I have three tables that I'm working with. </p>
  
  <ol>
  <li>AccountingLine - Holds the generic account details</li>
  <li>Budget - Holds the budget data for each AccountingLine  (Many rows per AccountingLine)</li>
  <li>Actual - Holds the actual cost data for each AccountingLine (Many rows per AccountingLine)</li>
  </ol>
  
  <p>I'm trying to get the results in a single query which will return ALL ROWS from the AccountingLine table, and SUM the Amounts for each AccountingLine from the Budget and Actuals table.  </p>
  
  <p>Using the SQL below, the SUM isn't working for the Budget or Actual data. If I remove one of the joins and one of the SUM functions then it calculates correctly for the single joined table.  Very strange... anyone run across this with multiple SUM functions on three or more tables in MySQL?</p>
  
  <pre><code>SELECT  A.*, SUM(B.`amount`) AS BudgetAmount, SUM(ACT.`amount`) as ActualAmount 
  FROM accounting_line A 
  LEFT JOIN budget B ON B.accounting_line_id = A.accounting_line_id
  LEFT JOIN actual ACT ON  ACT.accounting_line_id = A.accounting_line_id
  GROUP BY A.`accounting_line_id`
  </code></pre>
  
  <p>By issuing the statement above, I'd expect to see the accounting_line fields, the SUM of the Budget amounts for each accounting_line and the SUM of the Actual amounts for each accounting_line.  </p>
  
  <p>I've searched all over and can't find an instance of multiple SUM functions. Thanks so much for any advice.</p>
  
  <p>Josh</p>
  
  <p>Table Data is below:</p>
  
  <pre><code>    Table: AccountingLine
  
        act_line_id   department
      ----------------------------------  
                  1   Sales   
                  2   HumanResources
  
  
  Table: Budget
  
  
          budget_id   actg_line_id     amount
      ----------------------------------------------  
                  1              1          3500.00
                  2              2          5000.00
                  3              2          15000.00
  
  Table: Actual
  
  
          actual_id   actg_line_id     amount
      ----------------------------------------------  
                  1              1        1000.00
                  2              2         500.00
                  3              2        9000.00
  </code></pre>