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';
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
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?
17 People are following this question.
