CODEWITHSUNDEEP
sqlpostgresql
arkdevelopment
arkdevelopment

Reputation: 167

SQL Nested Aggregate Function

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)

Upvotes: 1

Views: 51

Answers (3)

Wiyanto Tan
Wiyanto Tan

Reputation: 78

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;

Upvotes: 0

Eric
Eric

Reputation: 3257

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'

Upvotes: 1

D-Shih
D-Shih

Reputation: 46249

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'

Upvotes: 1

Related Questions