SQL Server平均计数

一种方法是使用公共表表达式为 count over 避免使用 group by ,然后用 avg ,再次与 结束 像这样的事情应该可以做到:

;WITH CTE AS
(
    SELECT State 
          ,Location
          ,JobNumber -- and all the other columns
          ,COUNT(JobNumber) OVER(PARTITION BY State, Location) As CountOfJobsPerLocation
    FROM -- rest of the query here
)

SELECT  State 
       ,Location
       ,JobNumber -- and all the other columns
       ,AVG(CountOfJobsPerLocation) OVER(PARTITION BY State) As AvgJobsPerLocInState
FROM CTE

使用派生表或CTE分层查询:

Outer query that adds the 50+ columns
  FROM {Query that groups by State (only) and gets Average of Counts}
    FROM {Query that groups by State & Location and gets Count of JobNumbers}