Click here to Skip to main content
15,882,552 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Comments
AspDotNetDev 3-Nov-10 19:09pm    
I updated my answer to be a little more explicit.

Group by the loan ID, then use COUNT(*) to count the number of people in that group:
SQL
SELECT
    tbl_loans.loanID, COUNT(*) AS CountInGroup
FROM tbl_loans
JOIN tbl_Borrowers
    ON tbl_loans.loanID = tbl_Borrowers.loanID
GROUP BY
    tbl_loans.loanID

Note that it does not matter which loanID you choose so long as you are consistent between your GROUP BY and SELECT.
 
Share this answer
 
v2
Comments
Mastersev 3-Nov-10 18:27pm    
GROUP BY tbl_Loans.loanID OR GROUP BY tbl_Borrowers.loanID???
and can you show me where to put the count(*) exactly in the script?
Nish Nishant 3-Nov-10 19:12pm    
Voted 5, proposed as answer!
Something like this should work

SQL
SELECT tbl_loans.loanID, Count(*) 
FROM tbl_loans
Group By tbl_loans
 
Share this answer
 
I found the solution

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


loanID BorrowerLoanCount
----------- -----------------
1 3
2 2
3 1
4 1
 
Share this answer
 
Comments
Yusuf 3-Nov-10 22:49pm    
why do you need the inner join when no field is accessed from the second table?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900