question

nevada1978 avatar image
nevada1978 asked

how to calculate annual averages based on distinct combinations

So I have a table of 146510 rows. The schema and sample data are given below.      create table test2 ( year char(4) qtr char(2), area char(6), industry char(3), employment numeric(8,0) ); 2014,01,000019,722513,324 2014,02,000019,722513,330 2014,03,000019,722513,311 2014,04,000019,722513,347 2014,00,000019,722513,328 For every distinct combination of year, qtr, area, and industry, I need to produce a yearly average. The issue that I am running into is figuring out how to have it produce a 5th quarter. At the moment, there are 4 rows (due to having four quarters) per every combination of area and industry. I need to produce a fifth row that will have a q5 (or some value that represents average). First question is if there is a need to use the select distinct command? Second, do I use update to produce this fifth row?
selectssmspivotdistinct
2 comments
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.

can you post some example data and expected output?
0 Likes 0 ·
sample data has been added
0 Likes 0 ·

1 Answer

· Write an Answer
tzvikl avatar image
tzvikl answered
Hi Nevada, From the sample data you added it seems your looking to add a row which calculates the average of the quarterly averages (i.e. 328 = (324+330+311+347)/4). Meaning, you're trying to calculate a quarterly average per area and industry, and an average of all the quarterly averages. If that's the case try using this (adding here some sample data for testing): --1. create test table create table ##test2( year char(4), qtr char(2), area char(6), industry char(6), employment numeric(8,0) ) go --2. insert sample data insert into ##test2 values('2014','01','000019','722513',1456) insert into ##test2 values('2014','01','000019','722513',89) insert into ##test2 values('2014','01','000019','722513',956) insert into ##test2 values('2014','01','000019','722513',254) insert into ##test2 values('2014','01','000019','722513',258) insert into ##test2 values('2014','01','000019','722513',857) insert into ##test2 values('2014','01','000019','722513',749) insert into ##test2 values('2014','01','000019','722513',324) insert into ##test2 values('2014','01','000019','722513',569) insert into ##test2 values('2014','01','000019','722513',986) insert into ##test2 values('2014','01','000019','722513',748) insert into ##test2 values('2014','02','000019','722513',123) insert into ##test2 values('2014','02','000019','722513',326) insert into ##test2 values('2014','02','000019','722513',97) insert into ##test2 values('2014','02','000019','722513',568) insert into ##test2 values('2014','02','000019','722513',479) insert into ##test2 values('2014','02','000019','722513',236) insert into ##test2 values('2014','02','000019','722513',568) insert into ##test2 values('2014','02','000019','722513',914) insert into ##test2 values('2014','02','000019','722513',748) insert into ##test2 values('2014','02','000019','722513',856) insert into ##test2 values('2014','02','000019','722513',823) insert into ##test2 values('2014','02','000019','722513',316) insert into ##test2 values('2014','02','000019','722513',374) insert into ##test2 values('2014','02','000019','722513',915) insert into ##test2 values('2014','02','000019','722513',515) insert into ##test2 values('2014','02','000019','722513',478) insert into ##test2 values('2014','02','000019','722513',689) insert into ##test2 values('2014','02','000019','722513',95) insert into ##test2 values('2014','03','000019','722513',45) insert into ##test2 values('2014','03','000019','722513',589) insert into ##test2 values('2014','03','000019','722513',986) insert into ##test2 values('2014','03','000019','722513',257) insert into ##test2 values('2014','03','000019','722513',748) insert into ##test2 values('2014','03','000019','722513',236) insert into ##test2 values('2014','03','000019','722513',569) insert into ##test2 values('2014','03','000019','722513',78) insert into ##test2 values('2014','03','000019','722513',102) insert into ##test2 values('2014','03','000019','722513',311) insert into ##test2 values('2014','03','000019','722513',115) insert into ##test2 values('2014','03','000019','722513',214) insert into ##test2 values('2014','03','000019','722513',1024) insert into ##test2 values('2014','03','000019','722513',145) insert into ##test2 values('2014','03','000019','722513',515) insert into ##test2 values('2014','03','000019','722513',614) insert into ##test2 values('2014','03','000019','722513',319) insert into ##test2 values('2014','03','000019','722513',895) insert into ##test2 values('2014','04','000019','722513',785) insert into ##test2 values('2014','04','000019','722513',5623) insert into ##test2 values('2014','04','000019','722513',748) insert into ##test2 values('2014','04','000019','722513',896) insert into ##test2 values('2014','04','000019','722513',254) insert into ##test2 values('2014','04','000019','722513',123) insert into ##test2 values('2014','04','000019','722513',789) insert into ##test2 values('2014','04','000019','722513',456) insert into ##test2 values('2014','04','000019','722513',56) insert into ##test2 values('2014','04','000019','722513',890) insert into ##test2 values('2014','04','000019','722513',384) insert into ##test2 values('2014','04','000019','722513',424) insert into ##test2 values('2014','04','000019','722513',364) insert into ##test2 values('2014','04','000019','722513',325) insert into ##test2 values('2014','04','000019','722513',312) insert into ##test2 values('2014','04','000019','722513',141) --3. calcultae average + average of averages /*calculate quarterly average*/ select year , qtr , area , industry , avg(employment)'AvgEmployment' from ##test2 group by year , qtr , area , industry union /*calculate averages average*/ select year ,'05'as qtr , area , industry, avg(AvgEmployment) from ( select year , qtr , area , industry , avg(employment)'AvgEmployment' from ##test2 group by year , qtr , area , industry )t1 group by year , area , industry On the other hand, if your'e trying to calculate a yearly average (not the average of the 4 quarters average, but the average of the whole year per area and industry) then try using this query using grouping sets(based on the same sample data as above): --4. quarterly and yearly average per area and industry: select year , isnull(qtr,'05') , area , industry , avg(employment)'AvgEmployment' from ##test2 group by GROUPING sets ( (year , qtr , area , industry) ,(year , area , industry) ) Hope this helped Regards
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.

@tzvikl, I am getting the following error when I use the first query above. Any ideas as to why this is occurring?

Msg 207, Level 16, State 1, Line 21 Invalid column name 'employment'.

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.