sum function using subquery

Question

I have three tables:

Customer

customer_id   first_name
1              kapil
2              rajesh

Account

Customer_id     Account_id
1                   S1
2                   s2

Receipt

Recipt_id   customer_id   Account_id   Transaction_type   Amount
R1            1              s1         Deposit           40000
R2            2              s2         Deposit             300
R3            1              s1         withdrawal         2000

now i am putting query as follow

select   
    c.customer_id,c.first_name,s.current_balance,s.account_id,
    (select sum(amount) 
    from receipt as r ,
        saving_account as s 
    where r.transaction_type='deposit'
        and r.account_no = s.account_id
    ) as debit,
    (select sum(amount) 
    from receipt as r ,
        saving_account as s
    where r.transaction_type='withdrawl'
        and r.account_no = s.account_id  
    )as credit 
from customer as c 
left outer join saving_account as s 
inner join receipt as r on r.customer_id = s.customer_id 
on s.customer_id = c.customer_id 
group by c.customer_id

but it is giving me debit a single value for whole row and credit tooo i am not understanding why it is showing like that ....

My desired result is:

customer_id customer_name account_id debit credit balance
1           kapil         s1         40000   2000  200
2           rajesh        s2         300     null  500

There's no correlation between the sub selects and the customer — Laurence
– Laurence, Commented Nov 1, 2012 at 9:42
there is no need of customer table because customer table is just storing the id — Ashishsingh
– Ashishsingh, Commented Nov 1, 2012 at 9:44
@Ashishsingh can you my answer below? hope it's the one you are looking :) — John Woo
– John Woo, Commented Nov 1, 2012 at 9:51

John Woo · Accepted Answer · 2012-11-01 09:49:15Z

5

SELECT  customer_ID, first_name, Account_ID,
        Deposit, Withdrawal, 
        (Deposit - Withdrawal) balance
FROM 
(
    SELECT  a.customer_ID, 
            a.first_name,
            b.Account_ID,
            SUM (CASE WHEN c.transaction_type = 'Deposit' THEN c.Amount ELSE 0 END) Deposit,
            SUM (CASE WHEN c.transaction_type = 'withdrawal' THEN c.Amount ELSE 0 END) Withdrawal
    FROM    customer a
            INNER JOIN Account b
                ON a.customer_ID = b.customer_ID
            LEFT JOIN Receipt c
                ON b.customer_ID = c.customer_ID AND
                    b.account_ID = c.Account_ID
    GROUP BY a.customer_ID, a.first_name, b.Account_ID
) bb

SQLFiddle Demo

answered Nov 1, 2012 at 9:49

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cristian Lupascu · Accepted Answer · 2012-11-01 09:47:58Z

Try this:

select
  c.customer_id, 
  c.first_name,
  a.account_id,
  sum(case 
        when r.transaction_type='deposit' then r.Amount 
        else 0 
      end) as Debit,
  sum(case 
        when r.transaction_type='withdrawal' then r.Amount 
        else 0 
      end) as Credit,

  sum(case 
        when r.transaction_type='deposit' then r.Amount 
        else 0 
      end)-
  sum(case 
        when r.transaction_type='withdrawal' then r.Amount 
        else 0 
      end) as balance
from customer c
join account a
  on c.customer_id = a.customer_id
join Receipt r
  on a.account_id = r.account_id
group by 
  c.customer_id, 
  c.first_name,
  a.account_id

You can also test it online.

Note: I did not know how to determine Balace, so I just computed it by Debit - Credit.

Laurence · Accepted Answer · 2012-11-01 09:53:42Z

1

Inferring the current_balance column on the saving_account table:

Select
  c.customer_id,
  c.customer_name,
  s.account_id,
  sum(case when r.transaction_type = 'Deposit' Then r.Amount Else 0 End) As debit,
  sum(case when r.transaction_type = 'Withdrawal' Then r.Amount Else 0 End) as credit,
  s.current_balance
From
  customer c
    left outer join
  saving_account s
    on c.customer_id = s.customer_id
    left outer join
  receipt r
    on s.customer_id = r.customer_id and s.account_id = r.account_id
Group By
  c.customer_id,
  c.customer_name,
  s.account_id,
  s.current_balance

edited Nov 1, 2012 at 9:53

answered Nov 1, 2012 at 9:48

Laurence

11k1 gold badge28 silver badges36 bronze badges

Collectives™ on Stack Overflow

sum function using subquery

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related