如何将一个查询的计数添加到另一个查询中

Question

我的第一个查询是这样的:

SELECT location, COUNT(*) as sections
FROM section
GROUP BY location

得到:

Answer 1

直接连接查询:

SELECT *
FROM
(
SELECT location, COUNT(*) as sections
FROM section
GROUP BY location
)
FULL OUTER JOIN
(
SELECT s.location, COUNT(*) as students
FROM enrollment e 
INNER JOIN section s ON s.section_id = e.section_id
GROUP BY s.location
) USING (location)
ORDER BY location;

另一种选择是按section、join和location分组。

SELECT
location,
COUNT(*) as sections,
SUM(students_in_section) AS students
FROM section s
LEFT JOIN
(
SELECT section_id, COUNT(*) as students_in_section
FROM enrollment
GROUP BY section_id
) e ON e.section_id = s.section_id
GROUP BY s.location
ORDER BY s.location;

另一种选择是连接表并计算不同的部分和不同的登记。

SELECT
location,
COUNT(DISTINCT s.section_id) as sections,
COUNT(DISTINCT e.enrollment_id) AS students
FROM section s
LEFT JOIN enrollment e ON e.section_id = s.section_id
GROUP BY s.location
ORDER BY s.location;

Answer 2

您可以使用COUNT(DISTINCT ...)来计算每个位置的唯一部分

SELECT location, COUNT (DISTINCT s.section_id) AS sections, COUNT (*) AS students
FROM enrollment e INNER JOIN section s ON s.section_id = e.section_id
GROUP BY location