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()
.