Subtract two columns of different tables

Question

I have two unrelated tables:

contribution(id,amount, create_at, user_id)

solicitude(id, amount, create_at, status_id, type_id, user_id)

I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative.

How can I do this? Function or query?
I tried this query:

SELECT  sum(contribution.amount)
- (SELECT sum(solicitude.amount) 
   FROM solicitude 
   WHERE user_id = 1 AND status_id = 1) as total
FROM contribution 
WHERE contribution.user_id = 1

Answer 1

I interpret your remark but that result can't to be negative as requirement to return 0 instead of negative results. The simple solution is GREATEST():

SELECT GREATEST(sum(amount)
      - (SELECT sum(amount)
         FROM   solicitude 
         WHERE  status_id = 1
         AND    user_id = 1), 0) AS total
FROM   contribution
WHERE  user_id = 1;

Otherwise, I kept your original query, which is fine.

For other cases with the possible result that no row could be returned I would replace with two sub-selects. But the use of the aggregate function guarantees a result row, even if the given user_id is not found at all. Compare:

• Get n grouped categories and sum others into one

If the result of the subtraction would be NULL (because no row is found or the sum is NULL), GREATEST() will also return 0.