SQL using count in case语句

Question

我正在尝试将计数小于5的所有活动分组(出于数据共享的原因)。

ID代码和ID名称给了我较高级别的编号，但一旦我包括“商店代码”，我就会得到一些去过一两次零星商店的客户的较低级别的编号。

Select  Count(*) [Activity],
        T.[ID Code],
        T.[ID Name],
        Case when Count(*) < 6 then 'Other Shop' Else T.Shop End [Shop Code]
From    MyTable T
Group By T.[ID Code],
         T.[ID Name],
         Case when Count(*) < 6 then 'Other Shop' Else T.Shop End

但很明显，我不能在case语句中使用count。我已经尝试了一些类似问题的解决方案，但都不起作用！

谢谢

Answer 1

问题出在GROUP BY

Select  Count(*) as [Activity],
        T.[ID Code],
        T.[ID Name],
        (Case when Count(*) < 6 then 'Other Shop' Else T.Shop
         End) as [Shop Code]
From    MyTable T
Group By T.[ID Code],
         T.[ID Name];

聚合函数(或带有聚合的表达式)不属于GROUP BY。这些值是在SELECT中计算的，而不是用于定义组。

Answer 2

您可以使用HAVING和UNION ALL语句，如下所示：

Select  Count(*) as [Activity],
    T.[ID Code],
    T.[ID Name],
    'Other Shop' [Shop Code]
 From MyTable T
Group By T.[ID Code],
      T.[ID Name]
having Count(*) < 6
 union all
Select  Count(*) as [Activity],
    T.[ID Code],
    T.[ID Name],
    T.Shop [Shop Code]
 From MyTable T
Group By T.[ID Code],
      T.[ID Name]
having Count(*) >= 6

Answer 3

select 
count(*) as activity,
code,
name,
Case when Count(*) < 6 then 'Other Shop' Else shopcode End as shopcode
from mytable group by code, name ,shopcode