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

`but that result can't to be negative`. Please elaborate. And what's wrong with the query you have? The syntax can be simplified, but it does the job nicely. — Erwin Brandstetter, Jun 15 '15 at 23:50

klin · Answer 1 · 2015-06-16T10:34:30.897

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

I think this is more expensive and verbose than it needs to be. — Erwin Brandstetter, Jun 16 '15 at 00:33
@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. — Erwin Brandstetter, Jun 16 '15 at 01:42
@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, Jun 16 '15 at 02:01
@ErwinBrandstetter - actually my solution with an outer query seems to be faster. — klin, Jun 16 '15 at 09:59

score 2 · Accepted Answer · edited May 23 '17 at 12:33

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.

score -1 · Answer 3 · answered Jun 15 '15 at 23:20

-1

You have to join the tables

SELECT sum(c.amount) - s.total 
FROM contribution c inner join  (SELECT user_id, sum(solicitude.amount) total
FROM solicitude  GROUP BY c.user_id HAVING user_id = 1 AND status_id = 1  )s
on c.user_id=s.user_id GROUP BY c.user_id,s.total HAVING c.user_id = 1

EDIT: I forgot an alias

answered Jun 15 '15 at 23:20

jurhas

613
1
4
12

Thanks for your time;) – Nelson Jean Pierre Jun 16 '15 at 00:28

Subtract two columns of different tables

3 Answers3