score:1

Accepted answer

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.

More questions

More questions with similar tag