question

The query below works great but only returns a single quarter of results. The table that it draws from is generated once per quarter. Each quarter a new version is created hence the date on the end, 201701 (2017 - First Quarter). I am trying to average the four quarters into one and therefore avoid excel. Below the first code section is a basic framework that I know about doing averages in SQL. However, the counted item, Number of Worksites, is getting me confused.

In the first item in the second code snippet, select AVG (q.column)... how do I specify a counted item?

;with sizeclasseptable as 
(
select area,ownership,sizeclassep from (
select '01' as sizeclassep, '50' as ownership
union select '02' as sizeclassep, '50' as ownership
union select '03' as sizeclassep, '50' as ownership
union select '04' as sizeclassep, '50' as ownership
union select '05' as sizeclassep, '50' as ownership
union select '06' as sizeclassep, '50' as ownership
union select '07' as sizeclassep, '50' as ownership
union select '08' as sizeclassep, '50' as ownership
union select '09' as sizeclassep, '50' as ownership) t0
cross join ( select distinct area from xyzfirms201704 ) t1
)


Select AVG (q.employment)
from 
(select employment
from
(SELECT
    '000000' AS area,
    t2.[SizeClassep],
    COUNT(*) AS [Number of Worksites],
    SUM(t2.Employment) AS [Employment In Size Class]
from sizeclasseptable
inner join xyzfirms201701 t2 
	on t2.area=sizeclasseptable.area 
	and t2.ownership=sizeclasseptable.ownership
	and t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
    t2.SizeClassep
ORDER BY
    t2.SizeClassep)

with CTE
as
(

…

)
select AVG (q.column)
from
(select column_list
from 1st_quarter_query
UNION select column_list
from 2nd_quarter_query
UNION select column_list
from 3rd_quarter_query
UNION select column_list
from 4th_quarter_query
) as q;