My first query looks like this:
SELECT location, COUNT(*) as sections
FROM section
GROUP BY location
which gives me:
LOCATION SECTIONS
-------------------------------------------------- ----------
L507 15
L214 15
L210 10
M500 1
L509 25
L500 2
L511 1
L211 3
L206 1
M311 3
M200 1
LOCATION SECTIONS
-------------------------------------------------- ----------
H310 1
Then I have a second query:
SELECT location, COUNT(*) as students
FROM enrollment e
INNER JOIN section s
ON s.section_id = e.section_id
GROUP BY location
which gives
LOCATION STUDENTS
-------------------------------------------------- ----------
L507 36
L214 36
L210 29
M500 5
L509 72
L500 14
L511 3
L211 10
L206 8
M311 11
M200 1
LOCATION STUDENTS
-------------------------------------------------- ----------
H310 1
What am I missing to add the second query into the first, so the result would be:
LOCATION SECTIONS STUDENTS
---------- ---------- ----------
H310 1 1
L206 1 8
L210 10 29
L211 3 10
L214 15 36
L500 2 14
L507 15 36
L509 25 72
L511 1 3
M200 1 1
M311 3 11
M500 1 5
CodePudding user response：
Simply join the queries:
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;
Another option is to group the enrollments by section, join and group by location then.
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;
Another option is to join the tables and count distinct sections and distinct enrollments.
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;
CodePudding user response：
You can use COUNT(DISTINCT ...) to count the unique sections for each location
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