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?
COUNT(*) AS ClientCount,
Client = CASE COUNT(*) WHEN '1' THEN T.Client ELSE '[Clients]' END
CASE COUNT(*) WHEN '1' THEN T.Client ELSE '[Clients]' END
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