GROUP BY - 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

SQL Server 2000 Forums

SQL Server Development (2000)

GROUP BY

Author

Topic

send2sev
Starting Member

5 Posts

Posted - 2010-11-03 : 18:37:56

Hi guys I have the following query

"SELECT tbl_loans.loanID,tbl_Borrowers.BorrowerName FROM tbl_loans
INNER JOIN tbl_Borrowers ON tbl_loans.loanID = tbl_Borrowers.loanID"

with the following result:

loanID BorrowerName
----------- --------------------------------------------------
1 Sevak
1 Arsineh
1 Sarmen
2 Arpa
2 Garen
3 Varoosh
4 Orbel

I need to add a GROUP BY and show how many persons have the same loadID
for example:
Load ID BorrowerName
------- ------------
1 3
2 2
3 1
4 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-03 : 18:40:57

SELECT l.loanID, COUNT(*) BorrowerLoanCount
FROM tbl_loans l
INNER JOIN tbl_Borrowers b
ON l.loanID = b.loanID
GROUP BY l.loanID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog