I have two very simple tables and I'm trying to achieve Table 3, as in this example:
Starting from how much every person had at the beginning, i'd like to know with how much each one is left after some expenses.
Do you have any suggestion? Thank you a lot!
Hey @Alex9000!
Happy to help here. This is definitely doable using Retool's UI, but I think a SQL query would actually be simplest to both set up and maintain.
You can write a subquery to aggregate the amount_spent for each account holder name in Table 2, then join it with Table 1 to calculate the remaining_balance. Here's an example query:
SELECT table1.account_holder, table1.balance - COALESCE(amount_spent, 0) AS remaining_balance FROM table1 LEFT JOIN ( SELECT account_holder, SUM(amount_spent) AS amount_spent FROM table2 GROUP BY account_holder ) AS t ON table1.name = t.name;
We use the COALESCE function to handle cases where there are no corresponding transactions for a given account holder (for example, if the amount_spent is null, we want to treat that as 0 since adding null to a number will cause problems 
). We're also using the SUM function in the subquery to sum the total amount_spent for each account holder in Table 2.
We're left joining the subquery with Table 1—I chose left join to ensure that all account holders in Table 1 are included in the final table (even if there are no transactions).
Finally, we're calculating the remaining amount by subtracting the summed amount_spent from the balance for each account holder.
Let me know if this works for you or if you have any questions! 
Thank you so much! That worked just fine!
Awesome!! Glad it worked for you.
Also, I love your profile picture 🥹🐶