My first query looks like this:
SELECT location, COUNT(*) as sections
FROM section
GROUP BY location
which gives me:
Add a comment
|
2 Answers
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;
3 Comments
astentx
Count distinct rowids of each table will provide more accurate result independently of the data model and identification of the primary key for each table
Thorsten Kettner
@astentx: This is a good advice.
astentx
... but should be noted that it is valid only for plain tables
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
1 Comment
Martin Smith
this isn't the same results. it will only count the sections that exist in enrollment. Combining the two queries will give the count of all sections in a location regardless of their representation in enrollment