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
andGROUP BY
columns need to be consistent. It doesn't make sense to includecancerstatus
in theSELECT
, unless it is also in theGROUP BY
.
More questions
- Using case and group-by at the same time
- Using DISTINCT and TOP at the same time
- How to concatenate string of a column and update the string in another table at the same time with using Cursor
- Primary and Foreign Key at the same time
- Group consecutive rows of same value using time spans
- how to get today's Date only and not with the time using sql
- dapper: Get result and count at same time using QueryMultiple
- Select all rows with the same value in column 1 but different values in columns 2 and 3 using SQL
- Simultaneous connections to SQL Server using the same name and password. Is there any problem with that?
- Retrieve last element of a column from each group and use it as first element of the same column in next group
- Group By Count and a field in the same query
- Select and Delete in the same transaction using TOP clause
- SQL - using the same table in the outer query and in the sub query
- Getting SQL deadlock when inserting and deleting at the same time
- Update column using the same column from another table and gruoping by another and make sum equal
- SQL Server : is there a way to insert into a table values using both SELECT and VALUES at the same time?
- SQL - Using Group By and Count to collect the total of unique two-field combinations
- How to return multiple columns of the same row in T-SQL using GROUP BY
- Get data and using subselect on the same table
- How do I Group By and Count a column with 2 values at the same time?
- Can I use like and between clauses at the same time in SQL Server
- Two application using SQL Server Compact edition at the same time
- I am running a query using fetch and offset but confused with the behaviour of same
- How to query table1 first and make second query with results+table2 at the same time
- When you back up a database and scripts are running at the same time, at what point in time does the backup reflect?
- How to retrieve the result value and a select statement of a stored procedure at the same time in C#?
- Case statement in sql using other selected columns in the same statement
- Help with sql query using case and group
- Using Case Statement to compare four columns and adding the earliest date to the new column
- SQL Server: query to get the data between two values from same columns and calculate time difference
More questions with similar tag
- policy numbers formats sql server
- Having Two Different SQL server 2012 in client?
- Does SQL Server have simplified cursor record fetching comparable to Oracle?
- Apply Pivot on multiple colums and rows
- Saving Dynamic query result into variable
- SQL Server timing out during reorganize index and update stats
- SQL Server query GROUPING with ROLLUP
- SQL Processor error
- Move SQL data from one table to another
- How do I combine these two tables so I have one which displays both State and Zip code?
- Entity Framework: how to find transaction isolation level in SQL Profiler?
- Error Creating Assembly in SQL Server 2008 R2
- SSRS: is there a way of passing the values of a parameter to a "Values" keyword in the SQL query?
- Is there a good way having two SQL Server Databases on Different machines synched daily?
- How to Pivot/Unpivot Something with SQL2000
- SQL Server 2008 R2 to MySQL Migration
- Pipe Delimited info Column
- SQL Statement - How can Improve speed with indexing
- SQL Server 2008 : how to use Subquery/CTE for calculated alias columns
- Why aren't my breakpoints hitting SQL In Visual Studio 2019 debugging script from network server
- SQL Syntax: Create a single table with column names created from data stored over multiple tables
- How to replace null with n/a in sql?
- Selecting same column twice from a single table but with different conditions
- SQL Server "SELECT COUNT(field) FROM tblname" returns null
- SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE
- passing different id automatically in sql
- convert date to YYMM format in sql for given date
- Static WHERE clause in SQLDatasource
- T-SQL to "Merge" two rows, or "Rekey" all FK relationships
- The file is on a network path that is not supported for database files
Source:
stackoverflow.com