view table IF CASE condition not work properly

user_9441629 0
Viewed 64

View Table: don't work properly. I have two tables

  1. Deposits

  2. withdraws

If:

Account no  = 101
balance = 100 $
Account no  = 102 
balance = 50 $

When i withdraw some money Like this account A/C 101 Withdraw Amount 10$
Now result : is minus all account balance

Current balance: 

Account no  = 101
balance = 90 $ -10 $ 
Account no  = 102 
balance = 40 $ -10 $ // But 50

every account minus Deposit balance

CREATE VIEW main_balance_new as select account_no as account_no,
 SUM(deposit_amount)-(select 
  CASE 
    WHEN withdraw_amount IS NULL THEN 0
   ELSE SUM(withdraw_amount)
  END
  from withdraws) as balance from deposits group by account_no

How can i solve this problem?

laravel-5 mysql php
1 Answers

I think you need to aggregate the deposit and withdrawal tables separately:

CREATE VIEW d_view AS
SELECT account_no, SUM(deposit_amount) AS deposit_sum
FROM deposits
GROUP BY account_no

CREATE VIEW w_view AS
SELECT account_no, SUM(withdraw_amount) AS withdraw_sum
FROM withdraws
GROUP BY account_no

CREATE VIEW main_balance_new AS
SELECT
    d.account_no,
    d.deposit_sum - COALESCE(w.withdraw_sum, 0) AS balance
FROM d_view d
LEFT JOIN w_view w
    ON d.account_no = w.account_no;

This answer leaves open the possibility that a given account only appears in the deposit, but not the withdrawal, table.

Edit:

If you need a view, then use separate views to get around the subquery problem.

user_18632290
Related