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 badges721 silver badges981 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

1

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

answered Aug 10, 2015 at 10:31

mxix

3,6591 gold badge18 silver badges25 bronze badges

Collectives™ on Stack Overflow

SQL: GROUP BY multiple columns with CASE statement

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related