CODEWITHSUNDEEP
t-sqlfloating-pointaveragerounding
J_J
J_J

Reputation: 45

T-SQL AVG rounding a float result

I have a big stored procedure, with some nested cursors fetching some parameters dynamically. In this part I get the problem:

DECLARE @Avg_Dev float;

SET @sqlstatement = 'SELECT @Avg_Dev = AVG([' + @QLabel + '])
                    FROM Imported_Data
                    WHERE BrandID = ''' + CAST(@BrandID AS nvarchar(max)) + ''' AND CountryID = ''' + CAST(@CountryID AS nvarchar(max))
                    + ''' AND [Year] = ''' + CAST(@Year AS nvarchar(max))
                    + ''' AND ' + CAST(@QLabel AS nvarchar(max)) + ' <> ''' + CAST(@NoAnswer AS nvarchar(max)) + ''' ' + @Query;
EXEC sp_executesql @sqlstatement, N'@Avg_Dev float output', @Avg_Dev output;
PRINT 'AVG Check'; PRINT @Avg_Dev;

@BrandID and @CountryID are GUID, @Year is int, @QLabel is int and @Query is nvarchar(max). @NoAnswer contains a special value that has not to be considered.

@QLabel contains the column that I'm processing. @Query contains a condition to apply to the where statement, and it's different for every @QLabel.

The problem is that @Avg_Dev always contains values rounded to the nearest integer, and I can't see why. @QLabel can be a value between 1 and 10.

I know this is not easy to read, but I didn't find any other way to process the table dynamically with every parameter changing often.

Upvotes: 0

Views: 610

Answers (1)

user3036342
user3036342

Reputation: 1045

The problem here is that the column (QLabel) is defined as an integer. So an AVG() would return an integer (ignoring the decimals) and pushing that into your float variable. Try this:

SET @sqlstatement = 'SELECT @Avg_Dev = AVG(convert(float,[' + @QLabel + '])) 
                    FROM Imported_Data 
                    WHERE BrandID = ''' + CAST(@BrandID AS nvarchar(max)) + ''' AND CountryID = ''' + CAST(@CountryID AS nvarchar(max)) 
                    + ''' AND [Year] = ''' + CAST(@Year AS nvarchar(max))
                    + ''' AND ' + CAST(@QLabel AS nvarchar(max)) + ' <> ''' + CAST(@NoAnswer AS nvarchar(max)) + ''' ' + @Query;

Upvotes: 1

Related Questions