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);
If you want three columns, you need to specify them in the SQL statement. The easiest way is probably
COUNT(DISTINCT)
: