CODEWITHSUNDEEP
sqloracle-database
bajistaman
bajistaman

Reputation: 72

Oracle SQL Join and Count Case

I have a course where students are enroled by year and they have records of attendance and I need to know from the total enroled per year how many actually attended to the course, what would be the SQL query needed to achieve that?

Expected output:

YEAR COURSE ENROLED ATTENDED
---- ------ ------- --------
2017 IT           3        2
2018 IT           2        0

Sample Oracle script:

--
DROP TABLE ATTENDANCE;
DROP TABLE ENROLMENT;
DROP TABLE STUDENT;
DROP TABLE COURSE;
--
CREATE TABLE COURSE (ID VARCHAR2(1), NAME VARCHAR2(20), PRIMARY KEY(ID));
CREATE TABLE STUDENT (ID VARCHAR2(1), NAME VARCHAR2(20), PRIMARY KEY(ID));
CREATE TABLE ENROLMENT (ID VARCHAR2(1), COURSE_ID VARCHAR2(1), STUDENT_ID VARCHAR2(1), YEAR VARCHAR2(4), PRIMARY KEY(COURSE_ID, STUDENT_ID, YEAR));
CREATE TABLE ATTENDANCE (ENROLMENT_ID VARCHAR2(1), PRIMARY KEY(ENROLMENT_ID));
--
INSERT INTO COURSE VALUES('1', 'IT');
INSERT INTO STUDENT VALUES('1', 'John');
INSERT INTO STUDENT VALUES('2', 'Mary');
INSERT INTO STUDENT VALUES('3', 'Peter');
INSERT INTO ENROLMENT VALUES('1', '1', '1', '2017');
INSERT INTO ENROLMENT VALUES('2', '1', '2', '2017');
INSERT INTO ENROLMENT VALUES('3', '1', '3', '2017');
INSERT INTO ENROLMENT VALUES('4', '1', '1', '2018');
INSERT INTO ENROLMENT VALUES('5', '1', '2', '2018');
INSERT INTO ATTENDANCE VALUES('1');
INSERT INTO ATTENDANCE VALUES('2');
COMMIT;
--

Upvotes: 1

Views: 68

Answers (1)

The Impaler
The Impaler

Reputation: 48865

How about:

select e.year, c.name, 
    count(distinct s.id) as enroled, 
    count(distinct a.enrolment_id) as attended
  from enrolment e
  join course c on e.course_id = c.id
  left join student s on s.id = e.student_id
  left join attendance a on a.enrolment_id = e.id
  group by e.year, c.name

Result:

year  name  enroled  attended
----  ----  -------  -----
2017  IT    3        2
2018  IT    2        0

Upvotes: 2

Related Questions