SQL 3 joins with group by and sum function

Question

I have 3 tables
Tbcodetable

    codevalue | codename | desc1 | desc2 
    1         | SATO     | NAG   | Naga
    2         | SATO     | BAG   | Baguio
    3         | SATO     | NCR   | Head Office

Tbmember

employeeno | capcon_accountno | savings_accountno | sato
1          | 00101            | 00201             | NCR
2          | 00102            | 00202             | BAG

Tbdeposit

employeeno | account_no | accountbalance
1          | 00101      | 1000
1          | 00201      | 5000
2          | 00102      | 1000
2          | 00202      | 5000

What I want to query is sum of capital and savings per sato in one query

codename | codevalue | desc1      | desc2 | capcon | savings
SATO     |3          |Head Office |NCR    |1000    | 5000
SATO     |2          |Baguio      |BAG    |1000    | 5000
SATO     |1          |Naga        |NAG    |0       | 0

The best query I could do is

SELECT codename,codevalue,desc1,desc2,sum(b.accountbalance) as capcon 
  FROM TBCODETABLE c 
  left join TBMEMBER a on c.desc2 = a.SatoCode 
  join tbdeposit b on a.employeeno = b.employeeno 
 where a.SLAStatus = 'A' and c.codename ='sato'
group by codename, codevalue, desc1, desc2

which gives me this result

codename | codevalue | desc1      | desc2 | capcon
SATO     |1          |Head Office |NCR    |1000
SATO     |2          |Baguio      |BAG    |1000

This only gives me the sum of capital(Im lost on how include both savings and capital on result.) and it doesnt include the naga region

I could join tbmember and tbdeposit by employeeno but then I dont know how to separate sum of capital and savings in the result set

Edit
I edited the query and now Im able to query both thanks to syed but I still cant get the NAG sato code. Query:

SELECT codename,codevalue,desc1,desc2, coalesce(sum(b.accountbalance),0) as savings, coalesce(sum(bb.accountbalance),0) as capcon FROM TBCODETABLE c left join TBMEMBER a on c.desc2 = a.SatoCode join tbdeposit b on a.SAVINGS_AccountNo = b.AccountNo 
inner join tbdeposit bb on a.CAPCON_Accountno = bb.AccountNo where a.SLAStatus = 'A'
group by codename, codevalue, desc1, desc2

I am able to get the expected result thank you guys chris's left join advice solved my NAG SATO problem though Syed's reply helped me big time.
I dont how to upvote a reply so i'll just upvote chris(first time here.)

My final query is :

SELECT codename,codevalue,desc1,desc2, coalesce(sum(b.accountbalance),0) as savings, coalesce(sum(bb.accountbalance),0) as capcon FROM TBCODETABLE c left join TBMEMBER a on c.desc2 = a.SatoCode left join tbdeposit b on a.SAVINGS_AccountNo = b.AccountNo 
left join tbdeposit bb on a.CAPCON_Accountno = bb.AccountNo where a.SLAStatus = 'A' or codename = 'sato'
group by codename, codevalue, desc1, desc2

In `Tbcodetable` you have *`codevalue` 1 is Naga* and *`codevalue` 3 is Head Office* but in resulting table it is vice versa. — user4003407, Feb 25 '17 at 09:36
And, BTW, why do you need grouping here? IMHO, you just need to join `Tbdeposit` twice. [link](http://sqlfiddle.com/#!9/6a7613/8) — user4003407, Feb 25 '17 at 09:41
Yeah I just realized I needed just 1 more join as the link @SyedMZulqarnain said. Though it still doesnt get the NAG sato in the result — Dekso, Feb 25 '17 at 09:58

score 0 · Accepted Answer · answered Feb 25 '17 at 09:20

First, when you join something against a table you LEFT JOINed you need to make that also a left join because otherwise you end up with something that is an inner join equivalent instead. That is not your only problem.

Second, for value comparison, case matters so you should have c.codename ='SATO' if your table sample is correct.

Third, you probably at that point, want to add a case statement inside your SUM so:

SUM(case 
    when account_no = capcon_account_no 
    THEN accountbalance ELSE NULL END)

You can do something similar then for your savings as well.

score 0 · Answer 2 · answered Feb 25 '17 at 09:29

I think you need to do a group by employeeno and account_no in a subquery. Then split capcon and savings into separate columns with a case when:

select codename,
       codevalue,
       desc1,
       desc2,
       sum( case when a.savings_accountno = b.account_no then accountbalance else 0 end ) as savings,
       sum( case when a.capcon_accountno = b.account_no then accountbalance else 0 end ) as capcon
from   tbcodetable as c
       left join
       tbmember as a
       on c.desc1 = a.sato
       left join
       (
         select employeeno,
                account_no,
                sum( accountbalance ) as accountbalance
         from   tbdeposit
         group by employeeno,
                account_no
       ) as b
       on a.employeeno = b.employeeno
 group by codename,
       codevalue,
       desc1,
       desc2

See SQLfiddle here

score 0 · Answer 3 · answered Feb 25 '17 at 09:34

try this

select codename,codevalue,desc1,desc2,coalesce(sum(accountbalance),0) as capcon,
coalesce(deposite,0) as saving 
from tbcodetable tc  
left join tbmember tb 
on tc.codevalue = tb.employeeno  
left join(select max(employeeno) as employee,max(accountbalance) as deposite,accountbalance,employeeno from tbdeposite tp group by employeeno)l 
on tb.employeeno = l.employeeno group by codevalue;

DEMO

SQL 3 joins with group by and sum function

3 Answers3