Using case and group-by at the same time

Admin 1
Viewed 52

I want to use case in SQL, but also using the right group by

SELECT patientname, patientid, cancerstatus,    
  CASE
    WHEN cancerstatus IS NOT NULL THEN 'In surgery'
    WHEN cancerstatus IS NULL THEN 'no surgery needed'        
  END case
FROM database (nolock)
WHERE biopsydate BETWEEN '2020-08-25' AND '2020-08-26'
GROUP BY patientname, patientID

So I want to a case expression to put every patient who had cancer biopsy in categories of needing surgery or not, and I also want to group patients using patientID and name (those two criteras are must have).

It's showing error around the case part, what did I do wrong?

case group-by sql-server sql
1 Answers

You need to repeat the case in the group by, I think:

SELECT patientname, patientid,
       (CASE WHEN PJMPSID is not null, THEN 'In surgery'
             WHEN cancerstatus is null then 'no surgery needed'    
        END) as status
FROM database
WHERE biopsydate between '2020-08-25' and '2020-08-26'
GROUP BY patientname, patientID,
         (CASE WHEN PJMPSID is not null, THEN 'In surgery'
               WHEN cancerstatus is null then 'no surgery needed'    
          END);

Some notes:

  • NOLOCK should not be used, unless you really know what you are doing.
  • You are apparently using SQL Server. You should tag the question.
  • The SELECT and GROUP BY columns need to be consistent. It doesn't make sense to include cancerstatus in the SELECT, unless it is also in the GROUP BY.
user_11440350
Related