Updating column in table with sum of another table

user_6819968 0
Viewed 188

I'm relatively new to SQL and have been unable to solve the following issue.

In brief, I have two tables. We can call the first table people:

name
--------
Margaret
Jim
Lola

The second is a list of expenses of different types associated with the different entities:

name     | type     | amount
---------+----------+-------
Margaret | personal |    100
Jim      | business |     50
Lola     | business |    275
Margaret | business |     75
Lola     | personal |     10
Margaret | business |    150
Lola     | personal |     50
Jim      | business |    150

I'd like to add two columns to the first table, so that it looks like this:

name     | personal_expenses | business_expenses
---------+-------------------+------------------
Margaret |               100 |               275
Jim      |                 0 |               200
Lola     |               275 |                60

Initially, my hope was that I would be able to accomplish this with the following query, after adding the personal_expenses and business_expenses columns:

UPDATE people
SET personal_expenses = (
    SELECT SUM(amount)
    FROM expenses
    GROUP BY name
);

I have also tried:

UPDATE people
SET personal_expenses = (
    SELECT SUM(amount)
    FROM expenses
    WHERE expenses.name = people.name
);

but likewise to no avail.

I've looked at several answers to similar questions, but when I attempt--admittedly, perhaps incorrectly--to replicate them in sqlite3 I get (very uninformative) syntax errors.

What is the proper way to achieve this disaggregated sum in sqlite3?

sql-update sqlite
1 Answers

You are very close! You forgot one more condition in your WHERE clause. Also the sums in your example are not correct based on the example data you gave in the expense table

ALTER TABLE `people` ADD COLUMN `personal_expense` INTEGER;
ALTER TABLE `people` ADD COLUMN `business_expense` INTEGER;

UPDATE people
SET personal_expense = (
      SELECT SUM(amount)
      FROM expenses
      WHERE expenses.type = 'personal'
        AND expenses.name = people.name ),

    business_expense = (
      SELECT SUM(amount)
      FROM expenses
      WHERE expenses.type = 'business'
        AND expenses.name = people.name )
user_6923310
Related