Task to update the parent table with aggregated values from the child table

[CoopperBackpack]

I would like to share a Task example to update the parent table with aggregated values from the child table.

The tables used in this example are not ideal but anyway, they showcase the idea.

For example, there is the ‘Students’ table that stores unique students.

Students can make payments so the ‘Payments’ table stores data about the payments made by students.

These tables are linked with the popular one-to-many type of relationships.

Use case: I need to calculate the sum of payments per student to store in the Students table (in the 'Payments_sum' field). Let`s say recalculation on a daily basis is acceptable.

The intuitive way to create a Task for this use case is the following: tables are joined, and the SUM() function is added.

However, this Task is not valid because in SQL it is impossible to use aggregate functions in update SET list.

Error message: An aggregate may not appear in the set list of an UPDATE statement. (error code: SQL157)

[CoopperBackpack]

One of the ways to create a Task for this use case is to add a so-called subquery (SELECT statement) and use this subquery in a JOIN.

The subquery returns a temporary table which is handled by the database server in memory. The temporary table from the subquery is given an alias (in this example the alias is 'subquery') so that we can refer to it in the outer select statement.

In the screenshot, you may see the temporary table that was returned as a result of the subquery for this particular example. 

 

This is the result of INNER JOIN. So, the Task uses the sum calculated in the subquery to map it with a corresponding ID in the 'Students' table:

The 'Students' table after the Task run:

You may see that the 'Payment_sum' field is empty for the student who has ID=3. This happens because there are no records in the 'Payments' table related to this student. So, the INNER JOIN doesn`t include the record related to this student.

 

In case you want to store 0.00 instead of a blank value, use the LEFT JOIN and CASE statement.

The subquery returns the same temporary table as in the example above, however, the final data set received as a result of LEFT JOIN is the following:
 

You may see that the LEFT table (Students) includes all the records, and the subquery includes matching records. Since there are no records related to a student who has ID=3, there are NULL values. And CASE statement helps to replace NULL with 0.

 

The 'Students' table after this version of Task was executed:

 

 

Another approach that should work is the following design: