SQL: GROUP BY multiple columns with CASE statement

Question

I'm new to SQL.

I'd like to use GROUP BY with a CASE statement, to group results in a particular way if @myboolean is true.

I've seen many examples of how to use GROUP BY and CASE BY with a single field, or how to use GROUP BY with multiple fields without a CASE statement.

I don't know how to combine the two. When I enclose the GROUP BY fields within the CASE statement, I get a syntax error:

Incorrect syntax near ','

So, this works:

GROUP BY /* This works with no enclosing CASE statement */ field1, field2, field3, field4

This produces a syntax error:

GROUP BY CASE WHEN (@myboolean=1) THEN field1, <-- ERROR HERE: Incorrect syntax near ',' field2, field3, field4 ELSE field1 END

I have already looked at these questions:

SQL: Group By with Case Statement for multiple fields: seems to be a GROUP BY either/or based on CASE, rather than group by multiple. No commas involved anyway.
Group by Multiple columns and case statement: maybe I'm thick, but I can't see how this includes a case statement in the GROUP BY clause
various others, less relevant

I'm using Microsoft SQL Server Management Studio.

Please note that I'm inheriting a very complex/long SQL statement, which I want to avoid altering too much. I don't want to split the query into two separate SELECT statements.

group by directly associates with aggregate we have to also manage select statement — wiretext
– wiretext, Commented Aug 10, 2015 at 10:31
your ease expression doesnt make sense, you are saying when @myboolean=1 then return field1,field2,field3,field4 , THEN clause should have one and only one value or expression. — M.Ali
– M.Ali, Commented Aug 10, 2015 at 10:34

Tim Schmelter · Accepted Answer · 2015-08-10 10:31:15Z

10

You could use...

GROUP BY field1, CASE WHEN @myboolean=1 THEN field2 END, CASE WHEN @myboolean=1 THEN field3 END, CASE WHEN @myboolean=1 THEN field4 END

If @myboolean is not 1 that evaluates to NULL which doesn't affect the result.

If it's in a stored-procedure you can also use an IF.

answered Aug 10, 2015 at 10:31

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rahul Dubey Over a year ago

If @myboolean will be 1 then should it true this all case condition?

Tim Schmelter Over a year ago

@RahulDubey: if @myboolean is 1 the CASE evaluates to true and the THEN clause returns the expression defined behind, so field2, field3 or field4.

mxix · Accepted Answer · 2015-08-10 10:31:48Z

You can try this way:

IF (@myboolean=1) BEGIN SELECT field1, field2, field3, field4 FROM myTable GROUP BY field1, field2, field3, field4 END ELSE BEGIN SELECT field1 FROM myTable GROUP BY field1 END

Collectives™ on Stack Overflow

SQL: GROUP BY multiple columns with CASE statement

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related