sql - 如何使用 count(*) 从另一个具有条件和分组依据的表中进行选择？

Question

我正在处理两张名为Studentsanddepartments

学生

StudentId    |     Name       |    Address    |  DepartmentId

    1        |    Simons      |    Los Angeles|   001
    2        |    Mark        |    Texas      |   001
    3        |    Kelly       |    Florida    |   002
    4        |    Philip      |    New York   |   002

部门

DepartmentId   |     Name       |    Date_Creation

    001        |    Maths       |    01/09/2021
    002        |    Physics     |    01/08/2021 

我真正需要的是从部门表中检索所有信息以及来自部门的学生人数（ group by ）。

预期成绩：

DepartmentId   |     Name        |  NumberOfStudents

    001        |    Maths        |        2 
    002        |    Physics      |        2

我试图执行查询，但它没有给我任何结果：

Select DepartmentId, Name, (select count(*)
from Students s, Departments d
where s.DepartmentId = d.DepartmentId) as NumberOfStudents 

我可以修改什么以获得所需的结果？

标签： sqlsql-servertsql

Answer 1

也许？

SELECT
  d.Name,
  COUNT(s.StudentId) AS cnt
FROM students AS s
INNER JOIN dept AS d
  ON s.DepartmentId = d.DepartmentId
GROUP BY d.Name