August 9, 2012 at 11:44 am
*Not homework* *study guide for exam*
I need to display the course reference numbers and enrollments for courses that are greater than the average using a subquery?
Here is the code to create the course table and the data that I have entered:
CREATE TABLE Course
(Course_ID CHAR(2),
Ref_Number CHAR(5),
Faculty_ID VARCHAR(2),
Term Char(1),
Enrollment INTEGER,
TotRev FLOAT );
INSERT INTO Course VALUES ('1', '12345', '3', 'A', 24, 12345.00 );
INSERT INTO Course VALUES ('2', '54321', '3', 'B', 18, 21435.00 );
INSERT INTO Course VALUES ('3', '13524', '1', 'B', 7, 1256.00 );
INSERT INTO Course VALUES ('4', '24653', '1', 'C', 29, 54421.00 );
INSERT INTO Course VALUES ('5', '98765', '5', 'A', 35, 246753.00);
INSERT INTO Course VALUES ('6', '14862', '2', 'B', 14, 9876.00);
INSERT INTO Course VALUES ('7', '96032', '1', 'C', 8, 863159.00);
INSERT INTO Course VALUES ('8', '81256', '5', 'A', 5, 98762.00);
INSERT INTO Course VALUES ('9', '64321', '2', 'C', 23, 2965.00);
INSERT INTO Course VALUES ('10','90908', '3', 'A', 45, 91724.00);
INSERT INTO Course VALUES ('11', '64321', '1a', 'C', 23, 2965.00);
INSERT INTO Course VALUES ('12','90908', '2a', 'A', 45, 91724.00);
And here is the code I have so far:
/* code
Select Ref_Number,
(Select Enrollment From course
Where Avg(Enrollment) < Enrollment) AS enrollments
From course
Order By Enrollments
*/
August 9, 2012 at 11:55 am
You might want to look at reversing the concept in your code and building the main data set to reflect tuples that contain higher than average Enrollment and using the correlated subquery to return the Ref_Number.
The way you have it now, there's no correlation between the Ref_Number and the Enrollment from the subquery.
Just a thought.
Erin
August 9, 2012 at 12:01 pm
The subquery should be on the WHERE clause.
Remember you're trying to compare a value against the average to filter information.
Do you need more tips? Can you change your query?
EDIT: You can't use Aggregate functions directly in the WHERE clause but you can return their result from a subquery.
August 9, 2012 at 12:04 pm
Kind of like this?
Select Avg(Enrollment) As 'Enrollments'
(Select Ref_Number From course
Where course.enrollment > 'Enrollments')
From course
Group by course.enrollment
August 9, 2012 at 12:10 pm
No, you're still putting the subquery in the field list.
Put it in the WHERE clause where you can compare the result of the subquery to your field and filter the results.
August 9, 2012 at 12:20 pm
I think this gets er done!:-D
Select Ref_Number, Enrollment
FROM Course
WHERE Enrollment > (Select Avg(Enrollment) FROM Course)
Order By Enrollment DESC
August 9, 2012 at 12:34 pm
Booya! Well done!
Erin
August 9, 2012 at 12:35 pm
so how would I go about changing it to a join
August 9, 2012 at 12:55 pm
Do you mean to use the subquery as a derived table?
August 9, 2012 at 1:15 pm
It needs to be something like this, only problem is I get an error message stating "Msg 147, Level 15, State 1, Line 4
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference".
/*code
Select course1.Ref_Number, course1.enrollment
From course As course1 JOIN Course As course2
ON course1.Ref_Number = course2.Ref_Number
Where course1.Enrollment > AVG(course2.EnrollMent)
*/
August 9, 2012 at 1:47 pm
*upDate* I got rid of the error message but it does not return any data??
Select course1.Ref_Number, course1.enrollment
From course As course1 JOIN Course As course2
ON course1.Ref_Number = course2.Ref_Number
Group by course1.Ref_Number, course1.Enrollment
Having course1.Enrollment > AVG(course2.EnrollMent)
August 9, 2012 at 2:03 pm
You cannot accomplish that using a self join as you did.
The only way I figure it out, would be with a derived table.
SELECT course1.Ref_Number, course1.enrollment
FROM Course As course1
JOIN (SELECT AVG(EnrollMent) AS enrollment_avg
FROM Course) As course2 ON course1.Enrollment > enrollment_avg
However, is not something I like. In 2005 and beyond, I would suggest a CTE.
WITH CTE AS (
SELECT Avg(Enrollment) enrollment_avg
FROM Course
)
SELECT Course.Ref_Number, Course.enrollment
FROM Course
JOIN CTE ON Course.enrollment > CTE.enrollment_avg
DROP Table Course
August 9, 2012 at 2:11 pm
joshphillips7145 (8/9/2012)
*upDate* I got rid of the error message but it does not return any data??Select course1.Ref_Number, course1.enrollment
From course As course1 JOIN Course As course2
ON course1.Ref_Number = course2.Ref_Number
Group by course1.Ref_Number, course1.Enrollment
Having course1.Enrollment > AVG(course2.EnrollMent)
You can figure out why you're not getting any data by using this query
Select course1.Ref_Number, course1.enrollment, AVG(course2.EnrollMent)
From course As course1 JOIN Course As course2
ON course1.Ref_Number = course2.Ref_Number
Group by course1.Ref_Number, course1.Enrollment
August 9, 2012 at 2:32 pm
Awesome! The Cte works!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply