All geek questions in one place

Cannot use aggregate or subquery in expression used for group by GROUP BY clause list

In the sql statement below, I get the following error: "You cannot use an aggregate or subquery in an expression used for a group on a GROUP BY clause list." 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
+6
source share
2 answers

If you did not include T.Client in GROUP BY , you can only include this field in an aggregate function. In your case, grouping by this field changes the logic, so the output (and is related to your attempt to group by CASE statement). Instead, wrap T.Client in an aggregate function.

Thus, your groups are all the same, and when there is only one line, according to your CASE test test, you know what result the aggregate function will perform.

 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
+10
source

You do not need to group this 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
+2
source

Source: https://habr.com/ru/post/911676/

More articles:


All Articles