Search code examples
google-app-maker

Join Cloud SQL data models with aggregated field

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.

My data models look like this (simplified).

Projects Table

projectID, projectName  , projectBudget
0001     , Project Alpha, 25000
0002     , Project Beta , 2000

Costs Table

projectID, costAccrued
0001     , 1000
0001     , 5000
0002     , 1000
0001     , 8000
0002     , 500
0002     , 300

Combined Table

I am trying to create a view that shows the data combined where costAccrued is summed.

projectID, projectName  , projectBudget, costAccrued
0001     , Project Alpha, 25000        , 16000
0002     , Project Beta , 2000         , 1800

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.

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.

SELECT projects.`projectID`, projects.`projectName`, projects.`projectBudget`, SUM(costs.`costAccrued`)
FROM projects
LEFT JOIN costs on projects.`projectId` = costs.`projectID`    
GROUP BY projects.`projectID`;

enter image description here

Solution

  • I solved this myself. The issue had to do with the output column names not matching the field names in the Calculated SQL model.

    The correct query looks like this (note each output column is labeled to match the names in my Calculated SQL model):

    SELECT projects.`projectID` as "projectId", projects.`projectName` as "name", projects.`projectBudget` as "budget", SUM(costs.`costAccrued`) as "costAccrued"
FROM projects
LEFT JOIN costs on projects.`projectId` = costs.`projectID`    
GROUP BY projects.`projectID`;

    Reference: https://developers.google.com/appmaker/models/cloudsql#calculated_sql_models