CODEWITHSUNDEEP
sqlsql-servergroup-bycase
user6947491
user6947491

Reputation:

Using case and group-by at the same time

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?

Upvotes: 2

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

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.

Upvotes: 1

Related Questions