CODEWITHSUNDEEP
sqloracle-databasecase
Lyndey
Lyndey

Reputation: 77

CASE statement still returning multiple rows

the following query is returning two rows per ID.

select distinct ID,
count (COURSE_IDENTIFICATION) as tot_course_id,

COUNT(case when course_level = 'G'
     then (COURSE_IDENTIFICATION)
     else '0'end) count_grad,

COUNT (case when course_level = 'U'
       then (COURSE_IDENTIFICATION)
       else '0'end) count_under

from ods.STUDENT_COURSE

where ACADEMIC_PERIOD = '201890'
and COURSE_BILLING_CREDITS >0.0
group by ID, course_level
order by 1

I receive the following results: 

ID      |TOT_COURSE_NUM |COUNT_GRAD |COUNT_UNDER
-------------------------------------------------
12345   |1              |1          |1
12345   |2              |2          |2

Is there any way to ensure both grad and undergrad totals are counted in the same row?

Upvotes: 1

Views: 8790

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Your immediate problem is that course_level is in the group by. A secondary problem is that the count() is incorrect. I think this should be sufficient:

select ID,
       count(*) as tot_course_id,
       sum(case when course_level = 'G' then 1 else 0 end) as count_grad,
       sum(case when course_level = 'U' then 1 else 0 end) as count_under
from ods.STUDENT_COURSE sc
where ACADEMIC_PERIOD = '201890' and
      COURSE_BILLING_CREDITS > 0.0
group by ID
order by 1;

count(<expression>) counts the number of non-NULL values. Your else '0' is still non-null, so it gets counted. sum() is a simple way to take care of this.

This version assumes that COURSE_IDENTIFICATION is not NULL. That seems like a reasonable assumption in a table called STUDENT_COURSE. It is easily modified to handle NULL values.

And, select distinct is almost never appropriate with group by.

Upvotes: 5

Nick
Nick

Reputation: 2514

In your group by statement, you are specifying course_level. Remove course_level from your group by statement and it should consolidate the rows.

Upvotes: 4

Related Questions