Average and format decimal 2

[Webkins]

Hello, I am trying to average and format. I started with this:

select avg(review_service_score) from mwo.dbo.mwo and the result is 8

Then I tried this:

select cast(avg(review_service_score)as decimal(18,2)) from mwo.dbo.mwo and the result is 8.00

The correct format and answer I am looking for is: 8.97

Thank you for any and all help, I am very close, I think...

 

[gmmastros]

The average function will operate on whatever data type you give it. If you give it integers, it will average integers and output an integer. To correct the problem, you need to case your data type inside the average function, like this:

avg([!]cast([/!]review_service_score[!] As decimal(18,2))[/!])

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Webkins]

That worked perfect but now I get the answer 8.97100.
Do I also need to round it now ?

 

[dhulbert]

Try

round(avg(cast(review_service_score As decimal(18,2))),2)

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Webkins]

With this round(avg(cast(review_service_score As decimal(18,2))),2) I am still getting many more decimal places than I need or want. It does round now, but does not limit the decimal places to only 2.

Thanks so much

 

[gmmastros]

Try this:

[!]Cast([/!]avg(cast(review_service_score As decimal(18,2)))[!] As Decimal(18,2))[/!]

The round function was rounding properly, but when you average decimal(18,2), you end up needing more precision than you started with so the output data type of the average function was still a decimal, but with more precision. Rounding that decimal does the rounding, but does not alter the data type. To reduce the precision, you need to re-cast it to decimal again.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Webkins]

PERFECT !
THANKS TO ALL !