SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert String to Int or Decimal Then Average


Convert String to Int or Decimal Then Average

Author
Message
dan.gaze
dan.gaze
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 80
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!
Daniel Bowlin
Daniel Bowlin
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8200 Visits: 2629
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
lynns
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 325
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
dan.gaze
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 80
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
dan.gaze
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 80
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
lynns
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 325
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))
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search