How can I use count(*) to select from another table with condition and group by?

Question

I'm working on two tables called Students and departments

Students

StudentId    |     Name       |    Address    |  DepartmentId

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

Department

DepartmentId   |     Name       |    Date_Creation

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

What I need exactly is to retrieve all information from Department Table with the number of students that are from the department ( group by ).

Expected results:

DepartmentId   |     Name        |  NumberOfStudents

    001        |    Maths        |        2 
    002        |    Physics      |        2

I have tried to execute the query but it did not give me any result:

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

What can I modify to get the needed result?

FYI, it's 2021, *long* past time you adopted the ANSI-92 Explicit JOIN syntax; it's been about for **29 years**. There's no need to be using that 1980's Implicit JOIN syntax. — Thom A, Sep 06 '21 at 08:26

score 1 · Answer 1 · answered Sep 06 '21 at 08:39

1

maybe?

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

answered Sep 06 '21 at 08:39

GrahamH

350
2
9

Charlieface · Accepted Answer · 2021-09-06T09:46:25.277

1

You could also use either an APPLY or a pre-grouped join

SELECT
  d.Name,
  s.cnt
FROM dept AS d
OUTER APPLY (
    SELECT COUNT(*) AS cnt
    FROM students AS s
    WHERE s.DepartmentId = d.DepartmentId
) s;
-- alternatively
SELECT
  d.Name,
  ISNULL(s.cnt, 0) cnt
FROM dept AS d
LEFT JOIN (
    SELECT s.DepartmentId, COUNT(*) AS cnt
    FROM students AS s
    GROUP BY s.DepartmentId
) s ON s.DepartmentId = d.DepartmentId;

edited Sep 06 '21 at 09:46

answered Sep 06 '21 at 09:32

Charlieface

52,284
6
19
43

Thank you but what I have 0 as the number of students in one department? how can I display this department? – abdou93 Sep 06 '21 at 09:40
OK modified that for you – Charlieface Sep 06 '21 at 09:46
Thank you @Charlieface , I have used this solution and it also worked for me . https://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate – abdou93 Sep 06 '21 at 09:50

How can I use count(*) to select from another table with condition and group by?

2 Answers2