question

nevada1978 avatar image
nevada1978 asked

How to average a counted field in SqL

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;
sql-server-2012countaverage
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jon Crawford avatar image
Jon Crawford answered

There's no date column to check that I can see in your query, but I assume one exists in there somewhere. Look at how the table gets created, replicate that to build it for the year. Assume something like the following:

SUM(CASE WHEN MONTH(someColumn) >0 AND MONTH(someColumn)<4 THEN value ELSE 0 END) AS Q1,

SUM(CASE WHEN MONTH(someColumn) > 3 AND MONTH(someColumn)<7 THEN value ELSE 0 END) AS Q2,

SUM(CASE WHEN MONTH(someColumn)>6 AND MONTH(someColumn)<10 THEN value ELSE 0 END) AS Q3,

SUM(CASE WHEN MONTH(someColumn)>9 THEN value ELSE 0 END) AS Q4

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In this case, each table xyzfirms201701 is created by dissecting and manipulating a fixed width file. The "date" column is called yrqtr char(6). However in each table, the yrqtr value is constant throughout.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.