带有GROUP BY id的T-SQL SELECT

I have table with 'People'

我有'人'的桌子

Columns

列

ID
ID
Name
名称
Age
年龄

And table with 'Notes':

和表''注释'：

Columns

列

ID
ID
Text
文本
FK_Author
FK_Author

I want to select count of notes for all authors from people table and their name and age, but I want to group it by PERSON ID, not name. There are many situations when people have same name, but ID is obviously always different.

我想从人员表及其姓名和年龄中选择所有作者的笔记数，但我想用PERSON ID对其进行分组，而不是名称。人们有相同名称的情况很多，但ID显然总是不同的。

EXAMPLE (input)

示例（输入）

PEOPLE:

人：

╔════╦═══════╦═════╗
║ ID ║ NAME  ║ AGE ║
╠════╬═══════╬═════╣
║  1 ║ John  ║  12 ║
║  2 ║ Annie ║  29 ║
║  3 ║ John  ║  44 ║
╚════╩═══════╩═════╝

NOTES:

笔记：

╔════╦═══════╦═══════════╗
║ ID ║ TEXT  ║ FK_AUTHOR ║
╠════╬═══════╬═══════════╣
║  1 ║ 'aaa' ║         1 ║
║  2 ║ 'aaa' ║         1 ║
║  3 ║ 'aaa' ║         2 ║
║  4 ║ 'aaa' ║         2 ║
║  5 ║ 'aaa' ║         3 ║
╚════╩═══════╩═══════════╝

Expected result:

预期结果：

╔═══════╦═════╦════════════╗
║ NAME  ║ AGE ║ TOTALCOUNT ║
╠═══════╬═════╬════════════╣
║ John  ║  12 ║          2 ║
║ Annie ║  29 ║          2 ║
║ John  ║  44 ║          1 ║
╚═══════╩═════╩════════════╝

When I select data I have to group by Name too if I want to select this column because if I dont, I get error.

当我选择数据时，如果我想选择此列，我也必须按名称分组，因为如果我不这样做，我会收到错误。

4 个解决方案

#1

Since you want to get all records from table People, you need to join it with Notes by using LEFT JOIN so any user without any record on Notes will be included in the list with thev value of totalCount with zero.

由于您希望从表People中获取所有记录，因此您需要使用LEFT JOIN将其与Notes连接，以便任何没有Notes记录的用户都将包含在列表中，其值为totalCount，其值为零。

SELECT  a.ID, a.Name, a.Age,
        COUNT(b.FK_Author) totalCount
FROM    People a
        LEFT JOIN Notes b
            ON a.ID = b.FK_Author
GROUP   BY a.ID, a.Name, a.Age

SQLFiddle Demo
SQLFiddle演示

To further gain more knowledge about joins, kindly visit the link below:

要进一步了解联接，请访问以下链接：

Visual Representation of SQL Joins
SQL连接的可视化表示

OUTPUT

╔════╦═══════╦═════╦════════════╗
║ ID ║ NAME  ║ AGE ║ TOTALCOUNT ║
╠════╬═══════╬═════╬════════════╣
║  1 ║ John  ║  12 ║          2 ║
║  2 ║ Annie ║  29 ║          2 ║
║  3 ║ John  ║  44 ║          1 ║
╚════╩═══════╩═════╩════════════╝

#2

SELECT P.ID,P.Name,P.Age,COUNT(N.ID)
FROM People P 
INNER JOIN Notes N ON N.FK_Author = P.ID
GROUP BY P.ID,P.Name,P.age

#3

you can use subquery also to complete this task as-

您也可以使用子查询来完成此任务 -

select people.Name,people.Age,(select count(notes.id) 
                                 from notes 
                                where notes.FK_Author= people.id)
from people;

#4

You could also get the count from the Notes table first and then left join with the People table like below.

您也可以先从Notes表中获取计数，然后像下面一样使用People表连接。

Fiddle-demo (thanks to J W for the fiddle)

小提琴演示（感谢J W为小提琴）

select name, age, Notate_Count
from People p left join (
        select fk_author, count(*) Notate_Count
        from Notes 
        group by fk_author ) x
     on p.Id = x.fk_author
order by name --Ordering by name here, change as required

|  NAME | AGE | NOTATE_COUNT |
------------------------------
| Annie |  29 |            2 |
|  John |  44 |            1 |
|  John |  12 |            2 |

#1

SELECT  a.ID, a.Name, a.Age,
        COUNT(b.FK_Author) totalCount
FROM    People a
        LEFT JOIN Notes b
            ON a.ID = b.FK_Author
GROUP   BY a.ID, a.Name, a.Age

SQLFiddle Demo
SQLFiddle演示