GROUP BY ON a Subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP BY ON a Subquery

Author

Topic

zokhox
Starting Member

4 Posts

Posted - 2011-05-23 : 02:30:35

Hi
I have 2 tables as bellow:
tbl_loan: memberID, loanAmount
tbl_deposit : memberID, depositAmount

I want to have a list of members who have not paid back their loan:
SUM(tbl_deposit.depositAmount)=SUM(tbl_loan.loanAmount)

Any help will be appreciated.
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-23 : 03:05:30

[code]
select *
from
(
select memberID, loan = sum(loanAmount) from tbl_loan group by memberID
) l
left join
(
select memberID, deposit = sum(depositAmount) from tbl_deposit group by memberID
) d on l.memberID = d.memberID
where loan <> deposit
or deposit is null
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-23 : 07:59:32

select memberId
from tbl_loan a join tbl_deposit b on a.memberid = b.memberid
group by memberid
having sum(b.depositamount) < sum(a.loanamount) -- this assumes that members can pay back the loan on a partial basis - if deposit is somewhat less, then select the member

thats assuming that members posses multiple loans and you care about the overall summation.

Where software development knowledge meets the reader

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources