Convert String to Int or Decimal Then Average

Question

Post reply

Convert String to Int or Decimal Then Average

dan.gaze

SSC Enthusiast

Points: 130
More actions
October 17, 2013 at 3:00 am

#284718

Hello all, hope you are well!
Just after a bit of assistance with an expression that's turning into a bit of a head scratcher...
I have a string column containing mostly numeric values, but also 'Unknown' values too. In an expression I am using an IIf statement to convert the 'Unknown' values to NULL, whilst leaving the numeric values as they are. Surrounding the IIf statement I then need to Average the result set. Which means the string column requires converting to either int or decimal in order to do the average.
The problem is, when converting string to int/decimal, the NULL values I have defined are then converted to 0. Which throws the average out.
Is there any way to convert the string column to allow averaging, whilst leaving the NULL's intact?
Expression is as follows: =AVG(CDec(IIf(Fields!SCORE.Value = "Unknown", Nothing, Fields!SCORE.Value)))
I'm using SQL Server 2012.
Cheers!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

I would probably use AVG and the OVER clause in TSQL to work this one out.

http://technet.microsoft.com/en-us/library/ms189461.aspx

Look at the examples under "Using the OVER clause with aggregate functions"

lynns Say Hey Kid Points: 695 More actions · Answer 2

I would try using the decimal conversion on only the numeric values.

=AVG(IIf(Fields!SCORE.Value = "Unknown", Nothing, CDec(Fields!SCORE.Value)))

I think converting "Nothing" to a decimal creates the zeroes that are causing the problems.

dan.gaze SSC Enthusiast Points: 130 More actions · Answer 3

Daniel Bowlin (10/17/2013)
I would probably use AVG and the OVER clause in TSQL to work this one out.
http://technet.microsoft.com/en-us/library/ms189461.aspx
Look at the examples under "Using the OVER clause with aggregate functions"

Hi Daniel, my preference was to handle the conversion in the SQL statement itself, but unfortunately I can't as the dataset column is used elsewhere. I could possibly create an additional column but I wanted to see if there was a simpler solution first (more for future use than anything!)

dan.gaze SSC Enthusiast Points: 130 More actions · Answer 4

lynns (10/17/2013)
I would try using the decimal conversion on only the numeric values.
=AVG(IIf(Fields!SCORE.Value = "Unknown", Nothing, CDec(Fields!SCORE.Value)))
I think converting "Nothing" to a decimal creates the zeroes that are causing the problems.

Thanks lynns, I thought that might have been it then...but unfortunately I'm getting '#Error' when applying the conversion that way.

lynns Say Hey Kid Points: 695 More actions · Answer 5

This was interesting. Regardless of where I introduced a conversion function within the expression it created an error for the non-numeric entries. This is a little different approach, but I think it will work.

=SUM(CDec(IIF(IsNumeric(Fields!SCORE.Value),Fields!SCORE.Value,0)))/SUM(IIF(IsNumeric(Fields!SCORE.Value),1,0))