CODEWITHSUNDEEP
mysqlsql
Matthew Melone
Matthew Melone

Reputation: 257

Using CASE WHEN to grab distinct count of records in another column

I'm a serious noob here, but I haven't been able to find an answer to my question. My table has three columns of interest. Students (student_name), classes (class_name) they have signed up for and whether they have completed (status) the class. I want to be able to ultimately calculate the rate of how many students have taken each class and passed it.

So I've tried this:

SELECT 
  class_name as Class,

  COUNT_DISTINCT(student_name) as Total_Students,

  COUNT(CASE WHEN status = "COMPLETED" 
    THEN 1, ELSE 0 END) as Total_Completes,

  Total_Completes / Total_Students as Completion_Rate

 ....

GROUP BY Class

But this gives me inflated #s because a single student can generate lots of COMPLETED records for a single class. What I need is the number of distinct students who have completed the class. I tried changing the wording so it would count distinct records of student_names nested in the CASE WHEN who had the COMPLETED status, but I couldn't figure out the right syntax, if it exists.

Thanks for any help!

Upvotes: 0

Views: 53

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52665

So you should be able to solve your problem with an Inline view.

SELECT
   class_name as Class,
   COUNT_DISTINCT(student_name) as Total_Students,
   SUM(CASE WHEN status = "COMPLETED" THEN 1, ELSE 0 END) as Total_Completes,
   Total_Completes / Total_Students as Completion_Rate
FROM
    (SELECT DISTINCT 
         class_name ,
         student_name,
         status 
    FROM 
         ClassTable) as t
GROUP BY 
     class

This inline view creates a distinct set of class, student and status. However since you still have the potential of "COMPLETE" and Not "COMPLETE" for the same student you'll still have to use COUNT_DISTINCT on student name

Upvotes: 2

Related Questions