SQL Server AVG Operation Help!!?

Question

Post reply

SQL Server AVG Operation Help!!?

heyzain

Old Hand

Points: 351
More actions
April 11, 2010 at 4:07 am

#386679

Hi Folks,
I have three columns (Col1,Col2,Col3) in an SQL Server 2005 View. Now, I want a fourth column that finds out the Average of Col1, Col2, Col3.
Note: The columns Col1, Col2, Col3 are of Varchar type.. i-e: each column can either contain numeric value or the value 'N/A' ... I want the average of columns having numeric values only.
Examples:
If Col1 = 10, Col2 = N/A , Col3 = 20 then in fourth column, i want the result 15 (i-e: (10+20)/2)
If Col1 = 10, Col2 = 14 , Col3 = 25 then in fourth column, i want the result 16.33 (i-e: (10+14+25)/3)
Excel's AVERAGE function does this job easily. Kindly assist how can this be done in SQL?
Regards,
Zain.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

There are multiple parts to the solution:

1. for each columns, convert numeric values to a numeric data type and non-numerics to null

2. sum the three columns after replacing nulls with zero

3. count the non-null columns

4. When the column count with numeric values is greater than Zero: divide the sum by the count

create table #work

(ColOnevarchar(8)

,ColTwovarchar(8)

,ColThreevarchar(8)

)

;

insert into #work

(ColOne , ColTwo , ColThree )

select'N/A','N/A','N/A' union all

select'10','N/A','20' union all

select'10','14','25'

;

SELECTColOne , ColTwo , ColThree

,CASE WHEN ColCnt > 0 THEN

( COALESCE (ColOne, 0 ) + COALESCE (ColTwo, 0 ) + COALESCE (ColThree, 0 ) )

/ CAST( ColCnt AS FLOAT )

ELSE NULL

END AS ColAverage

FROM(SELECT CASE WHEN ISNUMERIC(ColOne) = 1 THEN CAST(ColOne AS INTEGER) ELSE NULL END

, CASE WHEN ISNUMERIC(ColTwo) = 1 THEN CAST(ColTwo AS INTEGER) ELSE NULL END

, CASE WHEN ISNUMERIC(ColThree) = 1 THEN CAST(ColThree AS INTEGER) ELSE NULL END

, ( CASE WHEN ISNUMERIC(ColOne) = 1 THEN 1 ELSE 0 END

+ CASE WHEN ISNUMERIC(ColTwo) = 1 THEN 1 ELSE 0 END

+ CASE WHEN ISNUMERIC(ColThree) = 1 THEN 1 ELSE 0 END

)

FROM#WORK

)AS WorkNumeric ( ColOne , ColTwo , ColThree , ColCnt )

SQL = Scarcely Qualifies as a Language

heyzain Old Hand Points: 351 More actions · Answer 2

Dear Carl Federl,

Thanks a million for your help.. I really appreciate it !!

Regards,

Zain.

Paul White SSC Guru Points: 150468 More actions · Answer 3

heyzain (4/11/2010)
Note: The columns Col1, Col2, Col3 are of Varchar type.. i-e: each column can either contain numeric value or the value 'N/A' ... I want the average of columns having numeric values only.

Have you considered storing the data as a numeric type that allows NULLs?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi