IDENTITY full-time and part-time students-CodePudding

have a query and test CASE that shows the number of full-time and part-time students. A full-time student is enrolled in at least 4 courses. A part-time student is enrolled in at least 1 course, but no more than 3.

Although the query appears to work it seems a bit verbose. I was wondering if there is a more succinct way to rewrite the query. In addition, I can would like to display the students first/last names with each row that meets the criteria

Perhaps with something like this?

     , LISTAGG(
         NVL2(s.student_id, s.last_name || ', ' || s.first_name, NULL),
         '; '
       ) WITHIN GROUP (ORDER BY s.last_name, s.first_name) AS students

Below are my tables, data and query I would like to shorten if possible. Thanks to all who answer and for your expertise.


CREATE TABLE students(student_id, first_name,  last_name) AS
   SELECT 1, 'Faith', 'Aaron'  FROM dual UNION ALL
  SELECT 2,  'Lisa',  'Saladino' FROM dual UNION ALL
  SELECT 3,  'Leslee',  'Altman'   FROM dual UNION ALL
  SELECT 4, 'Patty',  'Kern'    FROM dual UNION ALL
  SELECT 5,  'Beth',  'Cooper'    FROM dual UNION  ALL
  SELECT 95,  'Zak',  'Despart'    FROM dual UNION  ALL
  SELECT 96,  'Owen',  'Balbert'    FROM dual UNION  ALL
   SELECT 97,  'Jack',  'Aprile'    FROM dual UNION  ALL
  SELECT 98,  'Nicole',  'Kramer'    FROM dual UNION  ALL
   SELECT 99,  'Jill',  'Coralnick'    FROM dual;

CREATE TABLE student_courses (student_id,course_id) AS
SELECT 1, 1 FROM dual UNION ALL
SELECT 2, 1 FROM dual UNION ALL
SELECT 3, 1 FROM dual UNION ALL
SELECT 4, 1 FROM dual UNION ALL
SELECT 5, 1 FROM dual UNION ALL
SELECT 1, 2 FROM dual UNION ALL
SELECT 2, 2 FROM dual UNION ALL
SELECT 3, 2 FROM dual UNION ALL
SELECT 4, 2 FROM dual UNION ALL
SELECT 5, 2 FROM dual UNION ALL
SELECT 1, 3 FROM dual UNION ALL
SELECT 2, 3 FROM dual UNION ALL
SELECT 3, 3 FROM dual UNION ALL
SELECT 4, 3 FROM dual UNION ALL
SELECT 5, 3 FROM dual UNION ALL
SELECT 97, 1 FROM dual UNION ALL 
SELECT 97, 3 FROM dual UNION ALL 
SELECT 97, 5 FROM dual UNION ALL
SELECT 97, 6 FROM dual UNION ALL
SELECT 98, 3 FROM dual UNION ALL 
SELECT 98, 4 FROM dual UNION ALL
SELECT 98, 5 FROM dual UNION ALL
SELECT 99, 2 FROM dual UNION ALL 
SELECT 99, 4 FROM dual UNION ALL
SELECT 99, 5 FROM dual UNION ALL
SELECT 99, 6 FROM dual;

WITH enrolled_student_course_counts AS (
  SELECT
    s.student_id
    , s.first_name
    , s.last_name 
    , COUNT(sc.course_id) AS course_count
  FROM students s
    LEFT JOIN student_courses sc
    ON s.student_id = sc.student_id
  GROUP BY
    s.student_id
    , s.first_name
    , s.last_name 
  HAVING COUNT(sc.course_id) > 0
)
, student_enrollment_statuses AS (
  SELECT
    student_id
    , first_name 
    , last_name 
    , CASE WHEN course_count >= 4 THEN 'full-time'
           WHEN course_count BETWEEN 1 AND 3 THEN 'part-time'
      END AS student_enrollment_status
  FROM enrolled_student_course_counts
)
SELECT
  UPPER(student_enrollment_status) AS student_enrollment_status
  , COUNT(student_enrollment_status) AS student_enrollment_status_count
FROM student_enrollment_statuses
GROUP BY student_enrollment_status;

CodePudding user response：

As you only need the numbers (and not any other data), shorten the query so that it searches only the student_courses table:

SQL> with temp as
  2    (select student_id,
  3            count(course_id) cnt
  4     from student_courses
  5     group by student_id
  6    )
  7  select
  8    sum(case when cnt <  4 then 1 else 0 end) part_time,
  9    sum(case when cnt >= 4 then 1 else 0 end) full_time
 10  from temp;

 PART_TIME  FULL_TIME
---------- ----------
         6          2

SQL>