SQLPLUS ORACLE select statement

Question

List the student number, subject code, year, and the term of the students who register to only ONE subject in 2012 session 3.

SELECT StudentNum, RSubjectCode, Year, SessionTerm 
 FROM <TABLE> 
WHERE Year ='2012' 
  AND SESSIONTERM = '3';

This selects everyone from year 2012 and session 3. How do I only find out the student who registered ONE subject only?

You should think of a better title for your question. That one is totally useless — user330315
– user330315, Commented Nov 5, 2012 at 11:43

GolezTrol · Accepted Answer · 2012-11-05 11:46:04Z

3

Group by StudentNum, Year and SessionTerm. That way, you can count the number of record to only return those that have one record. Because of that grouping, you'll need to get max (or min) on the subject code. The max of 1 record will be just that only record, so the result will be right and the query will be fast, even though it may look odd.

SELECT 
  StudentNum, 
  max(RSubjectCode) as RSubjectCode, 
  Year, 
  SessionTerm 
FROM 
  <TABLE> 
WHERE 
  Year ='2012' 
  AND SESSIONTERM = '3'
GROUP BY
  StudentNum, 
  Year, 
  SessionTerm 
HAVING
  count(*) = 1

answered Nov 5, 2012 at 11:46

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLPLUS ORACLE select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related