SQL分组依据 [英] SQL group by

问题描述

我有以下查询

从tbl_loans中选择SELECT tbl_loans.loanID，tbl_Borrowers.BorrowerName
内联tbl_Borrowers ON tbl_loans.loanID = tbl_Borrowers.loanID"

结果如下:

loanID BorrowerName
----------- --------------------------------------- -----------
1塞瓦克
1 Arsineh
1萨门
2阿帕
2加伦
3 Varoosh
4奥贝尔

我需要添加一个GROUP BY并显示有多少人具有相同的loadID
例如:
负载ID BorrowerName
------- ------------
1 3
2 2
3 1
4 1

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

推荐答案

按贷款ID分组，然后使用COUNT(*)计算该组中的人数:

Group by the loan ID, then use COUNT(*) to count the number of people in that group:

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

请注意，只要您在GROUP BY和SELECT之间保持一致，选择哪一个借贷ID都没有关系.

Note that it does not matter which loanID you choose so long as you are consistent between your GROUP BY and SELECT.

类似的事情应该起作用

Something like this should work

SELECT tbl_loans.loanID, Count(*) 
FROM tbl_loans
Group By tbl_loans

我找到了解决方案

从tbl_loans中选择SELECT tbl_loans.loanID，COUNT(*)BorrowerLoanCount
内联tbl_Borrowers ON tbl_loans.loanID = tbl_Borrowers.loanID
GROUP BY tbl_Loans.loanID


loanID BorrowerLoanCount
----------- -----------------
1 3
2 2
3 1
4 1

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

这篇关于SQL分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！