title: Join Cloud SQL data models with aggregated field
body: <p>I need to create a view for my app that displays the data from two Cloud SQL models in a single table with one field aggregated.</p>
  
  <p>My data models look like this (simplified).</p>
  
  <h2>Projects Table</h2>
  
  <pre><code>projectID, projectName  , projectBudget
  0001     , Project Alpha, 25000
  0002     , Project Beta , 2000
  </code></pre>
  
  <h2>Costs Table</h2>
  
  <pre><code>projectID, costAccrued
  0001     , 1000
  0001     , 5000
  0002     , 1000
  0001     , 8000
  0002     , 500
  0002     , 300
  </code></pre>
  
  <h2>Combined Table</h2>
  
  <p>I am trying to create a view that shows the data combined where costAccrued is summed.</p>
  
  <pre><code>projectID, projectName  , projectBudget, costAccrued
  0001     , Project Alpha, 25000        , 16000
  0002     , Project Beta , 2000         , 1800
  </code></pre>
  
  <p>I haven't been able to find any code examples where Cloud SQL tables are joined with an aggregated field but I'm given to understand that creating a Calculated SQL table is the correct way to do this.</p>
  
  <p>However, I'm stuck on how to write the SQL query. This is what I've tried to use but it doesn't work and I think there is something to do with parameters that I'm not understanding.</p>
  
  <pre><code>SELECT projects.`projectID`, projects.`projectName`, projects.`projectBudget`, SUM(costs.`costAccrued`)
  FROM projects
  LEFT JOIN costs on projects.`projectId` = costs.`projectID`    
  GROUP BY projects.`projectID`;
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/qmXTS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qmXTS.png" alt="enter image description here"></a></p>