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