CODEWITHSUNDEEP
sqlsql-server-2008
Wannabe Coder
Wannabe Coder

Reputation: 707

Using CASE and COUNT together in SQL Statement

I need to write a SQL statement that can return a column indicating three things. The three things are 'An Actual Class', 'Multiple Classes', 'No Classes Selected'. I have used a function to get this for me, but as the table records grew, this method slowed down quite a bit. Can someone show me how this can be done without sacrificing speed when the amount of records grow? I was thinking some type of case can count together, but cannot figure it out.

The data I need returned would look like this.

Student         Classes
Bugs Bunny      Multiple Classes
Daffy Duck      Biology 101
Porky Pig       No Classes Selected

Schema

Student
StudentId int
Name

Class
ClassId int
Name

StudentClass
StudentId
ClassId

Upvotes: 1

Views: 6308

Answers (4)

The Flash
The Flash

Reputation: 3

I don't use the stack, but this is drastically easier.

count(case when field='411' then 1 else null end) as cnt_411

Most of these are wrong. The reason is that COUNT will return the number of rows when NOT TRUE.

For example:

WHERE student=123

If the student has no classes this semester, it will return all the other rows by count. Count appears to work until it doesn't.

Either use:

sum(case when)

or

count(case when else null)

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40289

Another version, very similar to the prior two; fewer joins, and at most one Class will be looked up per student (might make a difference on truly large tables).

SELECT
  xx.Name
  ,case xx.ClassCount
     when 0 then 'No Classes Selected'
     when 1 then cl.Name
     else 'Multiple Classes'
   end  Classes
 from (--  Count classes per student
       select
          st.Name
         ,count(sc.ClassId) ClassCount
         ,max(sc.ClassId)   OneClass  --  If a student has one class, this is it
        from Students st
         left outer join StudentClass sc
          on sc.StudentId = st.StudentId
        group by st.Name) xx
  left outer join Classes cl
   on cl.ClassId = xx.OneClass

(Can't debug, hopefully no typos...)

(Can't debug, hopefully no more typos...)

Upvotes: 1

jmacinnes
jmacinnes

Reputation: 1609

Your schema should look like this

Student
StudentId int
Name

Class
ClassId int
Name

StudentClass
StudentId
ClassId

Then your query would look like:

select
   s.Name,
   CASE x.ClassCount when 0 then 'No Classes Selected' when 1 then c.Name else 'Multiple Classes'
from
   Student s
   inner join
(select
   StudentId, count(sc.ClassId) as ClassCount
from
   Student s 
   left outer join StudentClass sc on s.StudentId = sc.StudentId
group by
  s.StudentId) x on s.StudentId = x.StudentId
  left outer join StudentClass sc2 on sc2.StudentId = x.StudentId and x.ClassCount = 1
  left outer join Class c on c.ClassId = sc2.ClassId

Upvotes: 0

jenson-button-event
jenson-button-event

Reputation: 18941

I would look at using a subquery to get the student id and the class count then lookup again the class name when there's only one, something like:

 Select s.Name,  
     Case s.Count
       When 0 Then 'No Classes Selected' 
       When 1 Then c.className 
       Else 'Multiple Classes' 
     End
From (
    select s.id, s.name, count(cs.id) as Count
    From student s 
    left join studentclass cs on c.studentid = s.id
    group by s.id, s.name
) as s
left join studentclass cs on c.studentid = s.id
left join class c on c.id = cs.classid

Of course im old school, the subquery could be factored out into a common table expression (CTE).

Upvotes: 0

Related Questions