CODEWITHSUNDEEP
sqlsql-servergroup-by
user724981
user724981

Reputation: 45

SQL Server GROUP BY NULL

I am using SQL Server and I have to group by a few columns but only if a setting is true.

DECLARE @setting tinyint
SET @setting = 0

SELECT col1 FROM table1 
GROUP BY col1,
  CASE WHEN @setting = 1 THEN col2 ELSE NULL END

OR should I default to col1 that I always use to group by if the setting is set, so the code would would be 

CASE WHEN @test = 1 THEN col2 ELSE col1 END

It does actually work to use the NULL but I can't find an example on the internet to prove that it is correct usage.

Upvotes: 1

Views: 4936

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

SELECT col1 FROM table1 
GROUP BY col1,
  CASE WHEN @setting = 1 THEN col2 ELSE NULL END

Your usage is correct. You already know it works.
ELSE NULL is implied in CASE, so you could have written it as

GROUP BY col1, CASE WHEN @setting = 1 THEN col2 END

You could try to be coy with variants like

GROUP BY col1, @setting * col2 --for numeric col2
GROUP BY col1, COALESCE(NULLIF(@setting,1), col2)

But the CASE statement actually unrolls to a better, simpler plan.

Upvotes: 7

cctan
cctan

Reputation: 2023

DECLARE @setting tinyint
SET @setting = 0

SELECT col1 FROM table1 
GROUP BY col1,
    CASE WHEN @setting = 1 THEN col2 ELSE NULL END

your first example should work.
For further clarification, do you want to group by col1, col2 or group by col1 when @setting = 1?

edit: Your first example is correct.

Upvotes: 1

Related Questions