sql - 统计表中特定列的重复记录

Question

我是 SQL Server 的新手，我需要获取列中重复某个值的次数，并按重复次数和位置对其进行分组。

我的意思是:

Table ACTIVITY
id     orderID      Location       date   

x      order11       NewYork      xxxx     
x      order22       Miami        xxxx    
x      order11       LA           xxxx
x      order33       NewYork      xxxx   
x      order11       NewYork      xxxx
x      order22       Miami        xxxx
x      order22       NewYork      xxxx
x      order44       Miami        xxxx

我有这个:

Select [orderID], count(1) as CountOrder from [MobileService].[ACTIVITY]
Group by [orderID]
Order BY CountOrder Desc

并返回我:

orderID       CountOrder    

order11       3
order22       3
order33       1
order44       1

好的，很好，但是，我想按位置过滤

Select [NewsItemTitle], count(1) as xx from [MobileServiceExtra].[ACTIVITY]
Group by [NewsItemTitle]
Order BY xx Desc
WHERE [Location] = 'NewYork'

并返回关键字“WHERE”附近的错误语法。

所以，如果我按纽约过滤，我想得到以下结果

orderID      CountOrder       

order11      2         
order22      1
order33      1

我该如何解决这个问题？

Answer 1

您很接近，只是顺序错误:

Select [NewsItemTitle], count(1) as xx from [MobileServiceExtra].[ACTIVITY]
WHERE [Location] = 'NewYork'
Group by [NewsItemTitle]
Order BY xx Desc