SQL Nested Aggregate Function

Question

I have tables:

Student(sID, firstName, lastName, email, cgpa)
Course(cNum, name, dept, credit)
Offering(oID, cNum, dept, year, term, instructor)
Took(sID, oID, grade)

I'm trying to complete the question:

Find all courses for the term 2017F and the current enrolment

I currently have this query to grab the number of students enrolled in each course:

SELECT Took.oID, COUNT(*) AS enrolment
FROM Took
GROUP BY Took.oID
HAVING COUNT(*) > 0

Nested inside of this statement to grab the correct courses that I want the enrolment counts for:

SELECT oID
FROM Offering
WHERE Offering.year = 2017
AND Offering.term = 'F'

Both of which are nested inside of this query to tie everything together:

SELECT DISTINCT Offering.cNum, Course.name, (I WOULD LIKE COUNT(*) AS enrolment HERE)
FROM Offering NATURAL JOIN Course
WHERE Offering.oID IN (
            SELECT oID
            FROM Offering
            WHERE Offering.year = 2017
            AND Offering.term = 'F'
            AND oID IN (
                    SELECT Took.oID, COUNT(*) AS enrolment
                    FROM Took
                    GROUP BY Took.oID
                    HAVING COUNT(*) > 0))
GROUP BY Offering.cNum, Course.name;

My question is, how can I pass the resulting COUNT(*) AS enrolment from the furthest nested query to the initial query so that it can be displayed in the resulting projection? (This is homework)

Aside - consider renaming Took as this domain may not be clear 6 mos from now. Maybe CoursesTaken or StudentCourses? — Parfait
– Parfait, Commented Oct 3, 2018 at 16:05

D-Shih · Accepted Answer · 2018-10-03 16:03:00Z

1

If I understand correctly you can try to use a subquery in from with JOIN instead of where subquery.

Then you can get count column from the subquery.

SELECT DISTINCT Offering.cNum, Course.name,t1.enrolment
FROM Offering 
JOIN (
    SELECT Took.oID, 
        COUNT(*) AS enrolment
    FROM Took
    GROUP BY Took.oID
    HAVING COUNT(*) > 0
) t1 on t1.oID = Offering.oID
NATURAL JOIN Course
WHERE Offering.year = 2017 AND Offering.term = 'F'

edited Oct 3, 2018 at 16:03

answered Oct 3, 2018 at 15:58

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

arkdevelopment Over a year ago

This definitely pointed me in the right direction, and I can move on to fixing it up from here. Thank you! (I'll set the answer as accepted in 3 minutes - I have to wait)

Eric · Accepted Answer · 2018-10-03 16:03:58Z

1

Try this

SELECT c.*
    , (
        SELECT COUNT(*)
        FROM Took
        WHERE oID = o.oID
    ) AS theCount
FROM Course c
JOIN Offering o ON o.cNum = c.cNum
WHERE o.year = 2017 AND o.term = 'F'

answered Oct 3, 2018 at 16:03

Eric

3,2701 gold badge22 silver badges27 bronze badges

1 Comment

arkdevelopment Over a year ago

This is really helpful. I didn't know that you could put nested queries directly in SELECT. Thank you!

Wiyanto Tan · Accepted Answer · 2018-10-03 16:05:06Z

0

May be this

SELECT Course.name, Course.cNum, count(*) as enrolment
FROM Course
JOIN Offering ON Course.cNum = Offering.cNum
JOIN Took ON Offering.oID = Took.oID
WHERE Offering.year = 2017
AND Offering.term = 'F'
GROUP BY Course.name, Course.cNum
HAVING count(*) > 0;

answered Oct 3, 2018 at 16:05

Wiyanto Tan

781 silver badge3 bronze badges

Collectives™ on Stack Overflow

SQL Nested Aggregate Function

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related