无法在GROUP BY子句列表的表达式中使用聚合或子查询

In the below sql statement i get the following error

在下面的sql语句中,我收到以下错误

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

无法在GROUP BY子句列表的表达式中使用聚合或子查询。

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

2 个解决方案

#1

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.

除非在GROUP BY中包含T.Client,否则只能在聚合函数中包含该字段。在您的情况下,按该字段进行分组会更改逻辑,从而导出(并且与您尝试按CASE语句进行分组有关)。相反,将T.Client包装在聚合函数中。

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.

这样你的组仍然是相同的,当只有一行时,根据你的CASE语句的测试,你知道聚合函数将给出什么结果。

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

#2

You do not need to group by that CASE expression.

您不需要按CASE表达式进行分组。

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

#1