Help on taking the average for multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help on taking the average for multiple columns

Author

Topic

markofdiego
Starting Member

13 Posts

Posted - 2009-10-01 : 11:37:40

I need some help on how to take multiple averages on multiple columns of a table. I have a table that has many columns with integer values. This table can change width. I need to take the average of each of those columns. Example:

-------------------------------------------------
| history_ndx, t_stamp, level_1, temp_1, press_1 |
-------------------------------------------------
| 1 , 1:00:00 am , 60 , 275, 93 |
| 2 , 2:00:00 am , 65 , 280, 90 |
| 3 , 3:00:00 am , 70 , 285, 88 |
| 4 , 4:00:00 am , 75 , 290, 85 |
-------------------------------------------------

After query I want something like this. Max ndx and time stamp.

-------------------------------------------------
| history_ndx, t_stamp, level_1, temp_1, press_1 |
-------------------------------------------------
| 4 , 4:00:00 am , 67.5 , 282.5, 89 |
-------------------------------------------------

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-01 : 11:57:39

THis?

Sample Data

declare @t table (history_ndx int, t_stamp varchar(20), level_1 int, temp_1 int, press_1 int)
insert @t
select 1 , '1:00:00 am' , 60 , 275, 93 union all
select 2 , '2:00:00 am' , 65 , 280, 90 union all
select 3 , '3:00:00 am' , 70 , 285, 88 union all
select 4 , '4:00:00 am' , 75 , 290, 85

Query

select max(history_ndx),max(t_stamp),avg(level_1*1.0),avg(temp_1*1.0),avg(press_1*1.0)
from @t

markofdiego
Starting Member

13 Posts

Posted - 2009-10-01 : 12:02:48

Not quite. This table will be growing all the time. So I will grab the last say 10 entries. Then I want to get each of the averages. But I don't want to hard code it because the width will change in time. So today it may have 10 columns but next month it may have 13 columns. I want to make a stored procedure or function out of it so I can use it for different tables. Does that make sense?

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 12:38:06

Hey Markofdiego!

SELECT MAX(history_ndx) history_ndx
,MAX(t_stamp) t_stamp
,AVG(cast(level_1 as float)) level_1
,AVG(cast(temp_1 as float)) temp_1
,AVG(cast(press_1 as float)) press_1
FROM avgTest

This will give you the results you're after. As far as increasing the number of columns, I'd recommend leveraging the INFORMATION_SCHEMA.COLUMNS to dynamically create the query.

You'd want to get the columnName and datatype from the schema to know which columns to perform an AVG vs. the MAX on unless the MAX column will always be static. In your case the history_ndx.

Make sense???

markofdiego
Starting Member

13 Posts

Posted - 2009-10-01 : 12:45:33

mivey4

Yes that makes sense and I had just found some info on the INFORMATION_SCHEMA. I have not quite figured out how to do this dynamically yet. Do you have any suggestions on that?

markofdiego
Starting Member

13 Posts

Posted - 2009-10-01 : 13:26:11

So I don't have a problem building this part

'AVG(cast(level_1 as float)) level_1'
and putting them into a result.

But can I use this result in a query instead of having to loop through my results?

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 18:55:58

Try this code as a start point for what you're trying to accomplish. You can build on this as a foundation and create a procedure. A few points though, this code makes the following assumptions so you'll have to tweak it to meet your specific needs.

1. The first field will always be of datatype INT or DATETIME
2. If any fields detected in the provided tablename are of any other datatype, you'll need to dynamically compensate for that by implementing a GROUP BY Clause.

DECLARE @FirstColumnName VARCHAR(50)
,@tableName VARCHAR(100)
,@sqlTableBuild NVARCHAR(MAX)
,@sqlCmd VARCHAR(MAX)
,@columnCt INT

-- SET THE NAME OF THE TABLE YOU WANT TO
-- EXECUTE YOUR QUERY AGAINST
SET @tableName = 'avgTest'

SELECT @columnCt = COUNT(*)
FROM adhoc.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
AND DATA_TYPE IN ('DATETIME','INT')

SELECT TOP 1 @FirstColumnName = COLUMN_NAME
FROM adhoc.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName

-- Dynamically Create Your Table
CREATE TABLE #ExecutionScript
(script VARCHAR(1000))

INSERT INTO #ExecutionScript
SELECT 'CREATE TABLE ##DynamicTable'
UNION ALL
SELECT TOP 1 '(' + COLUMN_NAME + ' ' + UPPER(DATA_TYPE)
FROM adhoc.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
AND RTRIM(COLUMN_NAME) = RTRIM(@FirstColumnName)
UNION ALL
SELECT ',' + COLUMN_NAME + ' ' + UPPER(DATA_TYPE)
FROM adhoc.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
AND RTRIM(COLUMN_NAME) <> RTRIM(@FirstColumnName)
UNION ALL SELECT ')'

-- Concatenate all the rows into a single execution statement
SELECT @sqlTableBuild = STUFF((SELECT '' + script
FROM #ExecutionScript FOR XML PATH('')),2, 0, '')

-- Create Your Table Dynamically
EXEC(@sqlTableBuild)

-- Truncate table for next Dynamic T-SQL
TRUNCATE TABLE #ExecutionScript
SET @sqlTableBuild = ''

-- Dynamically build your SQL statement
IF @columnCt > 1
BEGIN
INSERT INTO #ExecutionScript
SELECT CASE DATA_TYPE
WHEN 'INT' THEN
'SELECT AVG(CAST(' + @FirstColumnName + ' AS FLOAT)) ' + @FirstColumnName
WHEN 'DATETIME' THEN
'SELECT MAX(' + @FirstColumnName + ') ' + @FirstColumnName
END
FROM adhoc.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName AND COLUMN_NAME = @FirstColumnName
UNION ALL
SELECT ',' + CASE DATA_TYPE
WHEN 'DATETIME' THEN 'MAX('+ COLUMN_NAME + ') ' + COLUMN_NAME
WHEN 'INT' THEN 'AVG(CAST(' + COLUMN_NAME + ' AS FLOAT))' + ' ' + COLUMN_NAME
ELSE '' END
FROM adhoc.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
AND RTRIM(COLUMN_NAME) <> RTRIM(@FirstColumnName)
UNION ALL
SELECT ' FROM ' + @tableName

SELECT @sqlTableBuild = STUFF((SELECT '' + script
FROM #ExecutionScript FOR XML PATH('')),2, 0, '')

EXEC(@sqlTableBuild)
END
-- IF THERE'S ONLY 1 COLUMN
IF @columnCt = 1
BEGIN
INSERT INTO #ExecutionScript
SELECT CASE DATA_TYPE
WHEN 'INT' THEN
'SELECT AVG(CAST(' + @FirstColumnName + ' AS FLOAT))' + @FirstColumnName
WHEN 'DATETIME' THEN
'SELECT MAX(' + @FirstColumnName + ') ' + @FirstColumnName
END
FROM adhoc.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName AND COLUMN_NAME = @FirstColumnName
UNION ALL
SELECT ' FROM ' + @tableName

SELECT @sqlTableBuild = STUFF((SELECT '' + script
FROM #ExecutionScript FOR XML PATH('')),2, 0, '')
PRINT @sqlTableBuild
EXEC(@sqlTableBuild)
END

DROP TABLE #ExecutionScript
DROP TABLE ##DynamicTable

Hope this helps you out and good luck!

markofdiego
Starting Member

13 Posts

Posted - 2009-10-02 : 13:58:06

Thanks' for the help. What I ended up doing was created a couple of tables and procedures to help with this. Tell me what you think.

I created two extra table's.
1) holds the columns with the first part of the string. This table is populated by a stored procedure that clears the table and resets the seed and then repopulates it.
2) holds the final query in a nvarchar. This is built by a different procedure.

Both of these will be called when ever there is a change in the original table. I believe this will reduce my overhead since the query will remain constant most of the time.

All I have left is to get my final procedure to work. I am inserting this into a different table that is a variable that is passed to the procedure and the values are using the execute statement.

Select @selectString = @selectString + @SourceTable + ' ORDER BY t_stamp DESC) AS q'

Insert Into @DestinationTable Values Execute sp_executesql @selectString

Problem right now is it gives me an error that I have not declared the variable @DestinationTable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources