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 
        CASE WHEN SUM(transactions.amount) IS NULL THEN 0 ELSE SUM(transactions.amount) END AS balance
FROM coins
Left outer  JOIN addresses ON addresses.coin_id = coins.id
Left outer  JOIN transactions ON transactions.address = addresses.address
Left outer JOIN users ON transactions.user_id = users.id
WHERE users.email = '[email protected]'

I have tried quite a few combinations of queries but no matter what I do I get 1 of two options

• rows are only returned for coins that the user has a transaction/address for
• rows are returned but contain the balance of all transactions in the database not just the ones belonging to that user.

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. I don't care about ownership, I want to display every coin in the table regardless if users own it or not.

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 
        CASE WHEN SUM(transactions.amount) IS NULL THEN 0 ELSE SUM(transactions.amount) END AS balance
FROM coins
Left outer  JOIN addresses ON addresses.coin_id = coins.id
Left outer  JOIN transactions ON transactions.address = addresses.address
Left outer JOIN users ON transactions.user_id = users.id
WHERE users.email = '[email protected]'

I have tried quite a few combinations of queries but no matter what I do I get 1 of two options

• rows are only returned for coins that the user has a transaction/address for
• rows are returned but contain the balance of all transactions in the database not just the ones belonging to that user.

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 
        CASE WHEN COALESCE(SUM(transactions.amount),0) IS NULL THEN 0 ELSE COALESCE(SUM(transactions.amount),0) END AS b2
FROM coins
Left outer  JOIN addresses ON addresses.coin_id = coins.id
Left outer  JOIN transactions ON transactions.address = addresses.address
Left outer JOIN users ON transactions.user_id = users.id
WHERE users.email = '[email protected]'

I have tried quite a few combinations of queries but no matter what I do I get 1 of two options

• rows are only returned for coins that the user has a transaction/address for
• rows are returned but contain the balance of all transactions in the database not just the ones belonging to that user.

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 
        CASE WHEN SUM(transactions.amount) IS NULL THEN 0 ELSE SUM(transactions.amount) END AS balance
FROM coins
Left outer  JOIN addresses ON addresses.coin_id = coins.id
Left outer  JOIN transactions ON transactions.address = addresses.address
Left outer JOIN users ON transactions.user_id = users.id
WHERE users.email = '[email protected]'

I have tried quite a few combinations of queries but no matter what I do I get 1 of two options

• rows are only returned for coins that the user has a transaction/address for
• rows are returned but contain the balance of all transactions in the database not just the ones belonging to that user.