How to Use SubQuery in In Clause?

Help with subquery in SELECT clause?

• I'm having difficulty with this query in Oracle SQL Developer. The way I have it makes sense in my mind, but my implementation must not be quite right. It's throwing a missing right parenthesis error, and when I remove the ORDER BY on line 9, it then throws the single-row subquery returns more than one row error. But I do need multiple rows to show up in that column. Can you please help me out? Query: List the location, number of sections taught in that location, and number of students enrolled in courses at that location. Sort by location. My implementation: SELECT Location, COUNT(s.Section_ID) AS Sections, **(SELECT COUNT(*) **FROM Enrollment E, Section s **WHERE e.Section_ID = s.Section_ID **AND Location IN ******(SELECT DISTINCT Location ******FROM Section) **GROUP BY Location **ORDER BY Location) AS Students FROM Enrollment E, Section s WHERE e.Section_ID = s.Section_ID GROUP BY Location ORDER BY Location Expected results: LOCATION / SECTIONS / STUDENTS H310 / 1 / 1 L206 / 1 / 8 L210 / 10 / 29 L211 / 3 / 10 ... Thanks for the help!

• Answer:

  This might be a bit clearer (assumes you have a lookup table of locations) SELECT l.Location, COALESCE(sCount, 0) AS Sections, COALESCE(eCount, 0) AS Students FROM Locations l LEFT OUTER JOIN (SELECT Location, COUNT(*) AS sCount FROM Section GROUP BY Location) s ON l.Location = s.Location LEFT OUTER JOIN (SELECT Location, COUNT(*) AS eCount FROM Enrollment GROUP BY Location) e ON l.Location = e.Location