CODEWITHSUNDEEP
sqloracle-database
Raghvendra
Raghvendra

Reputation: 134

Oracle SQL: Finding common subjects taken by students in each class

Below is student table DDL and DML

CREATE TABLE STUDENTS
(
  CLASS NUMBER,
  STUDENT_ROLLNO  NUMBER,
  SUBJECT VARCHAR2(50)
);

INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'MATHS');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'SCIENCE');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 1,  'ART');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'MATHS');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'FRENCH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 2,  'POETRY');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 3,  'ENGLISH');
INSERT INTO STUDENTS(CLASS, STUDENT_ROLLNO, SUBJECT) VALUES(1, 3,  'MATHS');

Now I need to find common subject chosen by each student of that class. For this case ENGLISH and MATHS would be the result.

I need to write query to fetch the required result as their are more class and result should be as per class.

Thanks.

Upvotes: 4

Views: 287

Answers (2)

MT0
MT0

Reputation: 167832

Without any self-joins:

SELECT class,
       subject
FROM   (
  SELECT s.*,
         COUNT( DISTINCT student_rollno ) OVER ( PARTITION BY class ) AS num_students
  FROM   students s
)
GROUP BY class, subject
HAVING   COUNT( DISTINCT student_rollno ) = MAX( num_students );

Output:

CLASS SUBJECT
----- -------
    1 ENGLISH
    1 MATHS

Upvotes: 1

CompEng
CompEng

Reputation: 7376

try this

 WITH SUB1 AS (
SELECT  
CLASS,SUBJECT,COUNT(*) NMB  FROM
STUDENTS
GROUP BY CLASS,SUBJECT
),SUB2 AS(
SELECT CLASS, MAX(NMB)MAX_NMB FROM
(SELECT  
CLASS,COUNT(*) NMB  FROM
STUDENTS
GROUP BY CLASS,SUBJECT
)
GROUP BY CLASS
)
SELECT SUB1.CLASS,SUB1.SUBJECT
FROM SUB1
INNER JOIN SUB2
ON SUB1.NMB=SUB2.MAX_NMB  AND SUB1.CLASS=SUB2.CLASS

Upvotes: 2

Related Questions