CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2014
SP1
SP1

Reputation: 1202

Calculate average of a row in SQL Server

I have the below table..the percent column is of type nvarchar

Data  Percent1 Percent2 Percent3
1     3%        4%        6%
2     6%        8%        7%
3     8%        6%        8%

I have to calculate the Avg per line so I get results like Data Avg

1    4.33%

I was trying to convert the %column into decimal so I can apply the average function 

select
Case WHEN Isnumeric([Percent1]) = 1
THEN CONVERT(DECIMAL(18,2),Replace([Percent1],'%',''))
ELSE 0 END AS Percent1
from DashboardData

but I am just getting 0 values..I am guessing the outer function is running before the inner for some reason. Can someone please tell me how I can achieve this.

I know the IsNumeric function will make it 0 but I tried it before that and I was getting an exception that type is not a number.

Thanks

Upvotes: 1

Views: 1549

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Is this what you want?

select dd.*, s.average      
from dashboarddata dd cross apply
     (select avg(try_convert(numeric(10, 2), replace(pc, '%', ''))) as average
      from values (percent1), (percent2), (percent3)) as v(pc)
     ) s;

Upvotes: 2

S3S
S3S

Reputation: 25112

SELECT ISNUMERIC('3%') will return 0, as will all the rest of your values, so your else condition will always be the result.

Just drop the %

select
   data, 
   (replace(Percent1,'%','') + replace(Percent2,'%','') + replace(Percent3,'%','')) * 1.0 / 3

Note, if any of these values are NULL you need to account for that because NULL + anything IS NULL.

Also, you don't want to lean on ISNUMERIC too heavy... it can produce some results you probably aren't expecting

select 
    ISNUMERIC('$')      --money which is a numeric value
    ,ISNUMERIC('1e4')   --scientific notation
    ,ISNUMERIC('45D-1') --old scientific notation
    ,ISNUMERIC('.')     --just the decimal portion of a float / decimal

Upvotes: 2

Related Questions