That one was just specific to my challenge - since I was using aggregate function (

sum

) to calculate total value of all records, I had to group them by something

Oh okay, so it's optional, it's just for your use-case?

'cause something i'm not sure to understand and which confuse me is how your ``GROUP BY`` is used. I'm not sure to understand why using it in the sum part instead of the global scope

It is needed inside, not on the global scope, because that's where we apply our aggregation

sql
UPDATE public."Wallet" w
SET balance = sub.balance
FROM (
  SELECT SUM(delta) AS balance
  FROM public."Transaction" t
  WHERE t."walletId" = new."walletId"
  GROUP BY t."walletId"
) sub
WHERE w.id = new."walletId";

In other words: When new transaction is created, it will be accessible via

new

variable. Since transaction has

walletId

(that's my schema model), I can get the Wallet that transaction was added to by comparing IDs:

sql
WHERE w.id = new."walletId";

Once I have that matching Wallet, next step is to update the balance. How to do it? Well, we do a sub-query. Inside a sub-query, we SELECT all transactions for a given

walletId

(new transaction + everything that happened in the past) and we perform aggregation to summarise your balance.

sql
SELECT SUM(delta) AS balance
FROM public."Transaction" t
WHERE t."walletId" = new."walletId"
GROUP BY t."walletId"

Then, we just substitute that in the original query after

FROM

, so we can actually select these fields:

sql
UPDATE public."Wallet" w
SET balance = sub.balance
FROM (
  SELECT SUM(delta) AS balance
  FROM public."Transaction" t
  WHERE t."walletId" = new."walletId"
  GROUP BY t."walletId"
) sub
WHERE w.id = new."walletId";

And we're set.

Does that help? 🤔