如何在共享同一 GROUP BY 的 SQL 中使用多个 COUNT 语句

Question

我有以下语句输出正确的数据

 SELECT COUNT (Section_ID) AS Sections
 FROM Section
 GROUP BY Location;

并且此语句也输出正确的数据

 SELECT COUNT (e.Section_ID) AS Students
 FROM Section s, Enrollment e
 WHERE s.Section_ID = e.Section_ID
 GROUP BY Location;

但是，当我像这样将这些陈述放在一起时

 SELECT Location, COUNT(s.Section_ID) AS Sections, COUNT (e.Section_ID) AS Students
 FROM Section s, Enrollment e
 WHERE s.Section_ID = e.Section_ID
 GROUP BY Location;

分区和学生现在共享相同的数据。如何将这些语句组合在一起？

Answer 1

首先，切勿在FROM子句中使用逗号。 始终使用正确、明确的JOIN语法。

其次，记住COUNT()做什么。 它计算非NULL值的数量。

做你想做的事的一种方法是使用 COUNT(DISTINCT) ：

SELECT Location, COUNT(DISTINCT s.Section_ID) AS Sections,
       COUNT(DIStINCT e.Student_Id) AS Students
FROM Section s JOIN
     Enrollment e
     ON s.Section_ID = e.Section_ID
GROUP BY Location;

我必须猜测Enrollment的主键是什么. 我猜Student_ID.