How to display location-based student and course counts?

Courses
1

Hey everyone! I’m stuck on a SQL problem. I want to create a query that shows three columns: Location, Course Count, and Student Count. Right now, I’ve got two subqueries that work separately, but when I combine them, things get messy. All the counts end up in the student column, and locations are listed twice. Here’s what I’ve tried:

SELECT DISTINCT place, learner_count
FROM (
    SELECT place, COUNT(learner_id) AS learner_count
    FROM class c JOIN enrollment e
    ON c.class_id = e.class_id
    GROUP BY place
UNION
    SELECT place, COUNT(class_number) AS class_count
    FROM class 
    GROUP BY place
    ORDER BY place
);

Can anyone help me figure out how to get the results I need? I’m aiming for a table that clearly shows the number of courses and students for each location. Thanks in advance for any tips!

2

hey liam, try this:

SELECT c.place AS Location,
       COUNT(DISTINCT e.learner_id) AS StudentCount,
       COUNT(DISTINCT c.class_number) AS CourseCount
FROM class c
LEFT JOIN enrollment e ON c.class_id = e.class_id
GROUP BY c.place
ORDER BY c.place;

hope it helps!

3

I’ve encountered similar issues before, and I think I can offer a solution that might work for you. Instead of using UNION, try this approach:

SELECT 
    c.place AS Location,
    COUNT(DISTINCT e.learner_id) AS StudentCount,
    COUNT(DISTINCT c.class_number) AS CourseCount
FROM class c
LEFT JOIN enrollment e ON c.class_id = e.class_id
GROUP BY c.place
ORDER BY c.place;

This query uses a LEFT JOIN to ensure all locations are included, even if they don’t have any enrolled students. It counts distinct learner IDs for the student count and distinct class numbers for the course count.

The GROUP BY clause ensures you get one row per location, and the ORDER BY sorts the results alphabetically by place. This should give you a clean table with Location, StudentCount, and CourseCount columns.

Let me know if this works for your dataset or if you need any further adjustments.

4

Hey Liam39! I get your frustration with SQL sometimes. It can be tricky to get everything lined up just right. :thinking:

Have you considered using a LEFT JOIN instead of UNION? That might solve your issue with duplicate locations. Something like this could work:

SELECT 
    c.place AS Location,
    COUNT(DISTINCT e.learner_id) AS StudentCount,
    COUNT(DISTINCT c.class_number) AS CourseCount
FROM class c
LEFT JOIN enrollment e ON c.class_id = e.class_id
GROUP BY c.place
ORDER BY c.place;

This way, you’re counting students and courses in one go, without repeating locations. The LEFT JOIN ensures you’ll see all locations, even if there are no enrolled students.

What do you think? Does this look like it might solve your problem? Let me know if you try it out - I’m curious to hear if it works for your specific setup!