CODEWITHSUNDEEP
mysqlsqlsql-servermysql-workbench
Shoxxer
Shoxxer

Reputation: 35

SQL average from multiple columns

How do I get the average from multiple columns?
for example:

Columns:   ID 125Hz 250Hz 500Hz 750Hz 1000Hz 1500Hz 2000Hz 3000Hz 4000Hz 6000Hz 8000Hz
Values:    1  92    82     63    83    32     43     54     56     54     34      54

I want to get the average of all the columns except the ID. How do I do that?

Upvotes: 0

Views: 9066

Answers (5)

Emilio Gort
Emilio Gort

Reputation: 3470

In SQL-SERVER you can use this

DECLARE @total int
DECLARE @query varchar(550)
DECLARE @ALLColumns VARCHAR(500)
SET @ALLColumns = ''

    ----Build the string columns
SELECT  @ALLColumns = @ALLColumns + '+' + '['+sc.NAME+']'
FROM sys.tables st
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
WHERE st.name LIKE '%YOUR_TABLE_NAME%' 
AND sc.NAME LIKE  '[0-9]%';--[0-9]% just get the column that start with number
    ----Get the total number of column, 
SELECT @total = count(*) FROM sys.tables st
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
WHERE st.name LIKE '%YOUR_TABLE_NAME%' 
AND sc.NAME LIKE  '[0-9]%';--[0-9]% just get the column that start with number

SET @query = 'SELECT SUM('+ SUBSTRING(@ALLColumns,2,LEN(@ALLColumns))+')/'
    +CAST(@total as varchar(4))+ ' AS [AVG] 
    FROM [YOUR_TABLE_NAME] 
    GROUP BY [ID]'

--SELECT @query 
EXECUTE(@query)

This will execute a query like this one: 

SELECT SUM([125Hz]+[250Hz]+[500Hz]+[750Hz]+[1000Hz]+[1500Hz]+[2000Hz]
           +[3000Hz]+[4000Hz]+[6000Hz]+[8000Hz])/11 AS [AVG] 
FROM [YOUR_TABLE_NAME] GROUP BY [ID]

UPDATE

Add a column to store the avg, I called it [AVG] and chage the value of @query to

SET @query = '
CREATE TABLE #Medition (ID int,[AVG] decimal(18,4))
INSERT INTO #Medition (ID,[AVG]) 
SELECT ID,SUM ('+ SUBSTRING(@ALLColumns,2,LEN(@ALLColumns))+')/'
+CAST(@total as varchar(10)) 
+ ' AS [AVG] FROM Medition GROUP BY ID
UPDATE YOUR_TABLE_NAME  SET YOUR_TABLE_NAME.[AVG] = #Medition.[AVG]
FROM YOUR_TABLE_NAME INNER JOIN #Medition ON YOUR_TABLE_NAME.ID =#Medition.ID
DROP TABLE #Medition
'

Note: Build queries string is a little ugly

Upvotes: 2

primehalo
primehalo

Reputation: 869

SELECT sum(125Hz + 250Hz + 500Hz + 750Hz + 1000Hz + 1500Hz + 2000Hz + 3000Hz + 
4000Hz + 6000Hz + 8000Hz)/11 as averageHz from TABLE

Upvotes: 0

wrecklez
wrecklez

Reputation: 347

this will display Average value of all that fields of each ID you have.

SELECT AVG(125Hz+250Hz+500Hz+750Hz+1000Hz+1500Hz+2000Hz+3000Hz+4000Hz+6000Hz+8000Hz) 
AS Average FROM table
GROUP BY ID

Upvotes: 0

wvdz
wvdz

Reputation: 16651

Another way to do it, without actually using the magic number 11, be it a little more verbose.

WITH t1 AS
(
    SELECT * FROM myTable
    WHERE (...) -- Should limit result to 1 row
),
t2 AS
(
    SELECT col1 FROM t1
    UNION ALL
    SELECT col2 FROM t1
    UNION ALL
    (...)
)
SELECT AVG(col1) FROM t2;

Upvotes: 0

michikot
michikot

Reputation: 142

You have to manually add the columns since there's no available built-in functions for horizontal aggregation.

select (125Hz+250Hz+500Hz+750Hz+1000Hz+1500Hz+2000Hz+3000Hz+4000Hz+6000Hz+8000Hz)/11 as aveHz from table_name

Upvotes: 2

Related Questions