question

nevada1978 avatar image
nevada1978 asked

Trying to selectively insert average values from one table and into another.

I have two tables INDUSTRY and INDUSTRYANNUAL. The first one contains data from quarters 1 to 4. The second will contain annual averages of the previous. I need to average 9 fields and leave 13 others alone. Below is my best attempt at the necessary coding. How and where would I specify to leave the others alone? Or does not that need to be specified? INSERT INTO INDUSTRYANNUAL (ALL 22 fields) SELECT avg(9 applicable fields) From DBO.INDUSTRY WHERE PERIODYEAR='2016';
sql-server-2008insertaverage
10 |1200

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

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
This looks as though it's calling for you to use the [OVER clause with the AVG][1] statement. It allows you to select all the rows of data you're interested in, and do the aggregates over blocks of it. An extremely-unhelpful example but one that shows the workings: SELECT OBJECT_NAME(object_id) AS TableName, column_id, name, MAX(column_id) OVER (PARTITION BY object_id) AS NumberOfColumnsInTheTable, AVG(column_id) OVER (PARTITION BY LEFT(OBJECT_NAME(object_id), 1)) AS AverageNumberOfColumnsForTablesBeginningWithLetter FROM sys.columns ORDER BY OBJECT_NAME(object_id), column_id; Without a little more information about your table structures, it's difficult to give you an actual answer, but hopefully the above will get you on the way. [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql
1 comment
10 |1200

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

nevada1978 avatar image nevada1978 commented ·
Pardon my naivete but the object_id, what would this be? The first line in my case would be SELECT DBO.INDUSTRY (object._id) AS DBO.INDUSTRYANNUAL?
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.