CODEWITHSUNDEEP
sql-servert-sql
shorton
shorton

Reputation: 353

SQL Server average count

Table contains

 state,
 location,
 job_number,
 (and many more columns)

I need to query the average count of job_numbers at the state's locations. In other words, for each state, I need to know the average count of jobs at each location.

So if there are 100 Alaska locations that have 3 jobnumbers and 100 Alaska locations that have 1 jobnumber, the result would be 2 for all Alaska rows. (100*3 + 100*1)/200.

Looking for Average Number Of Jobs per location in each state

Desired result

State   Location  JobNumber AvgJobsPerLocInState
Alaska  Loc1      Job1       2
Alaska  Loc1      Job2       2
Alaska  Loc2      Job3       2
Alaska  Loc2      Job4       2
Ohio    Loc3      Job5       1

Can't figure out how to do it. To complicate it, my final query has about 50 other columns in it. I don't want to group by all of them.

Upvotes: 0

Views: 64

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

Layer the queries with either derived tables or CTEs:

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}

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

One approach is to use a common table expression to add a column for count, using the over clause to avoid the need for a group by, and then select from that cte with avg, again with the over clause. Something like this should do the trick:

;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

Upvotes: 1

Related Questions