Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert String to Int or Decimal Then Average Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 3:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:31 AM
Points: 12, Visits: 74
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!
Post #1505539
Posted Thursday, October 17, 2013 7:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
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"
Post #1505723
Posted Thursday, October 17, 2013 8:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 125, Visits: 270
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.
Post #1505728
Posted Thursday, October 17, 2013 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:31 AM
Points: 12, Visits: 74
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!)
Post #1505799
Posted Thursday, October 17, 2013 10:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:31 AM
Points: 12, Visits: 74
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.
Post #1505801
Posted Sunday, October 20, 2013 12:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 125, Visits: 270
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))

Post #1506493
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse