如何计算双组后的重复数？

Question

我在MSSQL中有一个SQL视图，我正在使用VBA从Excel查询该视图。我感兴趣的两栏如下。在我按位置代码和描述分组之后，有些情况下我会得到重复的位置代码，这是非常好的。我如何得到一个列，它给我在组后面的重复计数？在下面的例子中，两所PB学校将在新的专栏中有2的，其余的将有1的。

这是我的密码：

SELECT LOCCODE as [Location Code], LOCATION as [Location Description]
FROM dbo.View_PendingAnalytes 
WHERE (COLDATE > DateAdd(Day, -90, GETDATE()))  
GROUP BY LOCCODE, LOCATION 

这是输出：

Location Code   |Location Description
----------------|---------------------------
LSCAKE-503      |La Salina press cake
MISSION         |Mission Linen Service
OOOCOMP         |Outfall composite
PB-SCHOOL       |123 Nowhere (BW TEST)
PB-SCHOOL       |456 Nowhere (BW TEST)
SABRA           |Sabra Dipping Co.
SLCAKE-503      |San Luis Rey centrifuge cake
SLRCLAIM        |SLR Reclaim Comp

谢谢。

Answer 1

您可以使用COUNT(*) OVER()

SELECT  LOCCODE as [Location Code], 
        LOCATION as [Location Description],
        COUNT(*) OVER(PARTITION BY LOCCODE) as N
FROM dbo.View_PendingAnalytes 
WHERE (COLDATE > DATEADD(DAY, -90, GETDATE()))  
GROUP BY LOCCODE, 
         LOCATION;