CODEWITHSUNDEEP
sqlsql-serversql-server-2008
AzaRoth91
AzaRoth91

Reputation: 283

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

In the below sql statement i get the following error

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

How can i get around this? 

SELECT
    T.Post,
    COUNT(*) AS ClientCount,
    Client = CASE COUNT(*) WHEN '1' THEN T.Client ELSE '[Clients]' END
FROM
    MyTable T
GROUP BY
    T.Post,
    CASE COUNT(*) WHEN '1' THEN T.Client ELSE '[Clients]' END

Upvotes: 14

Views: 72432

Answers (2)

MatBailie
MatBailie

Reputation: 86715

Unless you include T.Client in your GROUP BY, you can only include that field within an aggregate function. In your case, grouping by that field changes the logic, so that's out (and is related to your attempt to group by the CASE statement). Instead, wrap T.Client in an aggregate function.

This way your groups are still the same, and when there is only one row, as per your CASE statement's test, you know what result the aggregate funciton is going to give.

SELECT
  T.Post,
  ClientCount = COUNT(*) AS ClientCount,
  Client      = CASE COUNT(*) WHEN 1 THEN MAX(T.Client) ELSE '[Clients]' END
FROM
  MyTable T
GROUP BY
  T.Post

Upvotes: 13

Tomalak
Tomalak

Reputation: 338228

You do not need to group by that CASE expression.

SELECT
    T.Post,
    COUNT(*) AS ClientCount,
    CASE COUNT(*) WHEN '1' THEN MIN(T.Client) ELSE '[Clients]' END Client
FROM
    MyTable T
GROUP BY
    T.Post

Upvotes: 4

Related Questions