Getting wrong output for subtraction of columns in two tables in SQL statement

I have two tables, Deposit table and Withdraw. These two tables have a same column “game_name” which is foreign key to the Game table.

Game
game_name
Deposit
game_name
d_amount
d_bonus
Withdraw
game_name
w_amount

Sample data:

Deposit

[GameName] [d_amount] [d_bonus]
-------------------------------
  Mario       100        10
  Mario       200        20
  Mario       300        30

Withdraw

[GameName] [w_amount] 
----------------------
  Mario       30        
  Mario       50        
      

Desire output : (100+200+300+10+20+30) – (30+50) = 580

I need to write a sql statement to calculate the total credit of the game, (SUM of d.amount+d.bonus) – (SUM of w.amount) and that will be the total credit for the game Mario.

Here is my sql statement:

SELECT SUM(deposit.d_amount+deposit.d_bonus) - SUM(withdraw.w_amount) from deposit 
cross join withdraw 
where deposit.game_name and withdraw.game_name = "Mario"; 

I’m getting the wrong output. Please help me on this.

Answer

You can use union all and aggregation:

select game_name,
       sum(amount + bonus - w_amount)
from ((select d.game_name, d.amount, d.bonus, 0 as w_amount
       from deposits d
      ) union all
      (select w.game_name, w.amount, 0 as bonus, 0 w.w_amount
       from withdraw w
      )
     ) dw
group by d.game_name;

Note: This includes all game_name, even those that are in only one table.

Second, this assumes that the values in the columns are not NULL. If that is possible, then use COALESCE().

Leave a Reply

Your email address will not be published. Required fields are marked *