2

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?

jgaeb
  • 197
  • 8

1 Answers1

1

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 )
Kenneth
  • 535
  • 2
  • 17
  • 1
    Thanks! This worked. The issue I'm confronting now is that the actual database I'm working with has ~50,000 rows in the `expenses` table and ~10,000 in the `people` table. I don't know how the `sqlite3` backend works, but my sense is that this is fairly inefficient (bringing the entire table into memory once for each row). It's very fast to `SELECT SUM(amount) FROM people JOIN expenses ON people.name = expenses.name GROUP BY expenses.name;` this creates a single column table with the right number of rows. Isn't there some way to just add this column onto the original `people` table? – jgaeb Jan 10 '19 at 17:54
  • It's not clear at all what you are asking for. You already have columns in the `people` table for the expenses called `personal_expense` and `business_expense`. In order to speed up the `UPDATE` query above, you can create an index on expenses.name + expenses.type – Kenneth Jan 11 '19 at 19:11
  • Great, thanks--again, I really appreciate your help. – jgaeb Jan 13 '19 at 18:17
  • You're welcome. Give my answer an up-vote if you like it ;) – Kenneth Jan 14 '19 at 16:34