PostgreSQL SUM doesnt return values with 0 rows

Question

I have a transactions table, each transaction has an address field which references a row in an address table, each address in the address table has a coinID.

I want to get a sum of all transactions for EVERY coin for a specific user.

The problem I have is that if a user has 0 transactions or addresses that belong to a specific coin it is completely missing from the result. I need all coins in a coin table that have 0 transactions or addresses to return with a sum of 0.

SELECT coins.name, SUM(transactions.amount),coins.price_usd
            FROM coins
            LEFT JOIN addresses ON addresses.coin_id = coins.id
            LEFT JOIN transactions ON transactions.address = addresses.address
            LEFT JOIN users ON transactions.user_id = users.id
            WHERE users.email = '[email protected]'
            GROUP BY coins.name, coins.price_usd

The Impaler · Accepted Answer · 2018-06-13 21:17:32Z

4

This solution shows you what you want:

create table coins (
  id int,
  name varchar(10),
  price_usd int
);

insert into coins (id, name, price_usd) values (1, 'Pound', 2);
insert into coins (id, name, price_usd) values (2, 'Yen', 98);
insert into coins (id, name, price_usd) values (3, 'Euro', 3);

create table addresses (
  coin_id int,
  address int
);

insert into addresses (coin_id, address) values (1, 20);
insert into addresses (coin_id, address) values (3, 30);

create table transactions (
  address int,
  user_id int,
  amount int
);

insert into transactions (address, user_id, amount) values (20, 500, 123);
insert into transactions (address, user_id, amount) values (20, 500, 101);
insert into transactions (address, user_id, amount) values (30, 501, 456);

create table users (
  id int,
  email varchar(50)
);

insert into users (id, email) values (500, '[email protected]');
insert into users (id, email) values (501, '[email protected]');

select coins.name, sum(transactions.amount),coins.price_usd
  from coins
  join addresses on addresses.coin_id = coins.id
  join transactions on transactions.address = addresses.address
  join users on transactions.user_id = users.id
  where users.email = '[email protected]'
  group by coins.name, coins.price_usd
union all
select name, 0, 0
  from coins
  where id not in (
    select coin_id 
      from addresses
      join transactions on transactions.address = addresses.address
      join users on transactions.user_id = users.id
      where users.email = '[email protected]'
  );

Result:

name   sum  price_usd
-----  ---  ---------
Pound  224          2  -- the requested user
Yen      0          0  -- another user
Euro     0          0  -- coin with no transactions

edited Jun 13, 2018 at 21:17

answered Jun 9, 2018 at 19:11

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

LoganHenderson Over a year ago

Thank you. Not completely necessary but is there an easy way to have the value equal 0 instead of null in the case where there are no rows

The Impaler Over a year ago

You can use the COALESCE function, as in: COALESCE(my_value, 0): if my_value is null, it returns the second parameter (0).

LoganHenderson Over a year ago

this solution does not actually work. It will produce a row for all coins in the coin table, however, the sum(transactions.amount) will reflect the sum of every transaction for that coin (not just the ones where users.email is [email protected]).

LoganHenderson Over a year ago

I also achieved a solution using a cross join at the top of the query. Curious if u see any benefit to solution using Union instead of Cross Join

The Impaler Over a year ago

The cross join will read all rows from both tables. On the other hand the union will run twice by first reading a subset of the rows (maybe 50%?) and then reading another small subset of the rows (maybe 10%?). I guess the union may be faster; however, my take is the difference is negligible. I wouldn't care too much about it, unless you have millions and millions of rows.

Collectives™ on Stack Overflow

PostgreSQL SUM doesnt return values with 0 rows

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related