AzaRoth91 AzaRoth91 - 3 months ago 23
SQL Question

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

Answer

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
Comments