CODEWITHSUNDEEP
sql
ABC
ABC

Reputation: 2158

Finding the average age of all students enrolled in each different class

I am having trouble figuring out how instead of finding one single average for all the students enrolled in any classes, how to find the average's for each class that has students enrolled.

It is equivalent to finding each class that does have a student enrolled, calculate the sum, calculate the average, then move on to the next class which enrolled students. Then let's say there are three classes that have students enrolled in it, let's output those average ages for each of the three classes. Three results should be returned, not one.

What I thought about initially as an attempted solution

SELECT AVG(AGE) FROM STUDENTS;

The schema I am using is listed below, with primary keys highlighted in bold.

Any help is appreciated, greatly.

Finding the average age of all students enrolled in that class.

Sample Data - Students

INSERT INTO `Students` (`sid`, `name`, `major`, `age`) VALUES ('1', 'Raymond', 'CS', '20');
INSERT INTO `Students` (`sid`, `name`, `major`, `age`) VALUES ('2', 'Jack', 'CS', '22');
INSERT INTO `Students` (`sid`, `name`, `major`, `age`) VALUES ('3', 'Henry', 'CS', '21');

Sample Data - Enrollment

INSERT INTO `Enrollment` (`sid`, `cid`) VALUES ('1', '1');
INSERT INTO `Enrollment` (`sid`, `cid`) VALUES ('1', '2');
INSERT INTO `Enrollment` (`sid`, `cid`) VALUES ('2', '2');

Sample Data - Classes

INSERT INTO `Classes` (`cid`, `name`, `instructor`, `room`, `time`) VALUES ('1', 'Math', 'Jack Brown', '300', '11:20:09');
INSERT INTO `Classes` (`cid`, `name`, `instructor`, `room`, `time`) VALUES ('2', 'Math', 'Henrita Espen', '500', '12:20:09');

Database Name - DB

Expected Output - Can vary

If the same student is only one involved, grouping by id will cause an average of the same age.

20

Upvotes: 1

Views: 1579

Answers (1)

unfairhistogram
unfairhistogram

Reputation: 186

Something like this:

select avg(s.age), e.cid
from students s join enrollment e on s.sid = e.sid
group by e.cid

Upvotes: 2

Related Questions