How to subtract the sum of columns from different tables?

I have two tables:

• Withdrawals: account_id + amount

• Prizes: account_id + amount

I want to calculate a balance per account, probably in a VIEW.

I could have two queries:

SELECT account_id, SUM(amount) FROM prizes GROUP BY account_id

which gives me the total prize per account, and:

SELECT account_id, SUM(amount) FROM withdrawals GROUP BY account_id

which gives me all withdrawals for these accounts.

Now, I'd like to subtract them to return balances for my accounts. What is the most efficient way to create a view like that?

As an alternative to a full join, you could use UNION ALL to combine the two aggregated sets together, only the withdrawals set would be included with the negated results. You would then use the combined set as a derived table and aggregate the amounts per account_id once more:

SELECT
  account_id,
  SUM(amount) AS balance
FROM
  (
    SELECT
      account_id,
      SUM(amount) AS amount
    FROM
      prizes
    GROUP BY
      account_id

    UNION ALL

    SELECT
      account_id,
      -SUM(amount) AS amount
    FROM
      withdrawals
    GROUP BY
      account_id
  ) AS derived
GROUP BY
  account_id
;

I am not sure how this query would compare to the full join solution but it looks simple enough to me to qualify as a good starting point.

StackExchange Database Administrators Q#212977, answer score: 5