Number of students and courses in a location

Question

I am trying to build a statement that will give me an output of three columns. Location, Sections (count of sections in that location) and Students (count of students in that location). In my statement below my sub queries give the correct counts separately; however, when I add my top select statement I have all of the counts show up under the student column with the location listed twice.

SELECT DISTINCT location, student
FROM(
    SELECT location, COUNT(student_id) AS Student
    FROM section s INNER JOIN enrollment e
    ON s.section_id = e.section_id
    GROUP BY location
UNION
    SELECT location, COUNT(section_no) AS Sections
    FROM section 
    GROUP BY location
    ORDER BY location);

Answer 1

If you want three columns, you need to specify them in the SQL statement. The easiest way is probably COUNT(DISTINCT):

SELECT s.location, COUNT(DISTINCT s.section_id) as NumSections,
       COUNT(student_id) AS Student
FROM section s INNER JOIN
     enrollment e
     ON s.section_id = e.section_id
GROUP BY s.location;