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

Solution 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.

Solution 2

You can add an outer query to check the total value:

SELECT CASE WHEN total > 0 THEN total ELSE 0 END AS total
FROM (
    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
    ) alias;

This solution is OK, but I suggest an alternative approach. Check how this query works:

with contribution as (
    select user_id, sum(amount) as amount from contribution
    group by 1),
solicitude as (
    select user_id, sum(amount) as amount from solicitude
    where status_id = 1
    group by 1)
select 
    c.user_id, c.amount as contribution, s.amount as solitude,
    case when c.amount > s.amount then c.amount - s.amount else 0 end as total
from contribution c
join solicitude s on c.user_id = s.user_id;

---

I made a simple test, just out of curiosity, on this setup:

create table public.solicitude (
    id integer,
    amount numeric,
    create_at timestamp without time zone,
    status_id integer,
    type_id integer,
    user_id integer
);

create table public.contribution (
    id integer,
    amount numeric,
    create_at timestamp without time zone,
    user_id integer
);

insert into contribution (user_id, amount)
select (random()* 50)::int, (random()* 100)::int
from generate_series(1, 4000000);

insert into solicitude (user_id, amount, status_id)
select (random()* 50)::int, (random()* 100)::int, 1
from generate_series(1, 4000000);

Results (msecs):

Erwin's solution with greatest():  922, 905, 922, 904, 904, 904, 905, 912, 905, 922
My solution with an outer query:   796, 795, 814, 814, 815, 795, 815, 796, 815, 796

Comments

[Nelson Jean Pierre]

Thanks for your time;)

[Erwin Brandstetter]

I think this is more expensive and verbose than it needs to be.

[klin]

@ErwinBrandstetter - Do you mean the second query?

[Erwin Brandstetter]

@klin: Yes, the second in particular, CTEs are more expensive and not necessary here. But also the first: one more query level than necessary, and GREATEST() would simplify further.

[klin]

@ErwinBrandstetter - The second query is intentionally verbose. This is an easy to understand and easy to modify example of how to join aggregated tables. Besides, sometimes I feel the need to write something nice. And this query is simply beautiful, isn't it? As regards greatest, I fully agree. I just forgot that such a thing exists ;)

[klin]

@ErwinBrandstetter - actually my solution with an outer query seems to be faster.