SQL, trying to find which rows have multiple entries

Question

I work for a school system as a data guy, and in my latest report that I need to send to the state I need to send some data about students taking tests. Easy enough.

The problem arises from this fact: when teachers or administrators entered data concerning students with multiple special needs they sometimes filled it out right, which is by saying multiple disabilities, and sometimes they made individual entries for each special need. For instance a student might have a learning disability and an emotional disability and the admin would make two entries, rather than one.

My query, which is long so I won't bother with the whole thing, needs something like this:

select 

  stuff
  ,morestuff
  ,CASE
      When [**a student is in the list multiple times**]
           Then '**Multiple Disabilities**'
      When '01' 
           Then 'Blind'

and so forth.

In my head it seems easy, but when I try and commit it to code it falls apart.

Answer 1

This should be pretty generic to any RDBMS. This assumes there's a column called 'disabilityname' which would have held your value 01, and assumes there's a studentid column of some sort.

If a student appears multiple times in the subquery COUNT(*), it will return Multiple Disabilities, and otherwise return the disabilityname column assuming it has a single value.

SELECT
  stuff,
  morestuff,
  CASE
    WHEN cnt.numdisabilities > 1 THEN 'Multiple Disabilities'
    ELSE disabilityname
  END as disabilityname
FROM
  tbl JOIN (
    SELECT studentid, COUNT(*) AS numdisabilities FROM tbl GROUP BY studentid
  ) cnt ON tbl.studentid = cnt.studentid