SQL Server 2008 查询中按错误分组

Question

我有这个查询：

select row_number() over(order by Name) as Number,Name
,COUNT(Code) as Tqty from WorkTbl group by Name order by Tqty
,COUNT(Code) as Tqty2 from WorkTbl Where Status <> 'AA' and Status <> 'BB' group by Name order by Tqty2

并且收到错误：

关键字“as”附近的语法不正确。

但如果我运行此查询，它工作正常：

select row_number() over(order by Name) as Number,Name
,COUNT(Code) as Tqty from WorkTbl group by Name order by Tqty

我必须拥有查询中的所有列

如何做到这一点？

Answer 1

你可能需要类似的东西

select row_number() over(order by Name) as Number,Name
,COUNT(Code) as Tqty,
COUNT(CASE WHEN Status<>'AA' AND Status <>'BB' THEN Code END) AS Tqty2
from WorkTbl group by Name order by Tqty