• Status: Solved
• Priority: Medium
• Security: Public
• Views: 577

# How to convert integet to decimal

Hi;

how do i convert the following to decimal

count(distinct(integervalue))

i want to convert the above to decimal(5,2)

thanks,
sripri96
0
sripri96
• 6
• 5
• 5
• +4
1 Solution

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Maybe...

CAST(count(distinct(integervalue)) AS decimal(5,2)
0

Author Commented:
i tried that and it gives the following error:
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
0

Commented:
Why would/do you want to convert a Count (=always integer, no decimal part) into a decimal? (just asking stupid questions...)
0

Commented:
Try jimhorn's solution but increase the decimal value:

CAST(count(distinct(integervalue)) AS decimal(10,2)
0

Author Commented:
it is to calculate the average and get the average in ##.## value
0

Commented:
CONVERT( decimal(5,2) ,Count(...) )
or
CAST( Count(...) AS decimal(5,2) )
but still wondering why :)
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
>Why would/do you want to convert a Count
Softplus is correct.  If you're counting an average, it's still a count, which means integer with no decimal points.
0

Commented:
Ah, ok
CONVERT( decimal(5,2) ,AVG(integervalue) )
or the same as cast
0

Author Commented:
i get the same error

Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
0

Commented:
What's the result if you just SELECT COUNT(*)? Adjust the number of digits accordingly in the CAST.
0

Author Commented:
i get the same error when i changed to decimal(10,2)

Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
0

Commented:
CONVERT( decimal(10,2), AVG( CONVERT(float, integervalue) ) )
?
0

Author Commented:
by doing count(*) lets say i am getting 10 ...I need to put 10 in an variable that is declared as decimal(5,2)
0

Commented:
and what happens if you do just AVG( integervalue ), what do you get then?
0

Commented:
Is this what you are trying to do:

DECLARE @Count DECIMAL(5,2)

SELECT @Count = COUNT(DISTINCT IntegerValue)
FROM YourTable
0

Commented:
If yes, you will encounter this error:

Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

if the count(distinct integervalue) is greater than 999.
0

Author Commented:
for example 10/3 = 3...when i say avg(integervalue)
i need 10/3 = 3.33  here 10 is stored in a variable

thanks!
0

Commented:
try
CAST( count(distinct(integervalue))* 1.0 AS decimal(5,2) )
0

Commented:
you can make it (10 * 1.0)/3
i.e
( var1 * 1.0)/ var2
0

Commented:
CAST( count(distinct(integervalue))* 1.0 AS decimal(10,2) )
0

Commented:
try this:

select cast(10 as decimal(10,2)) / 3

so, if 10 is stored in a variable:

select cast(@count as decimal(10,2)) / 3
0

IT ManagerCommented:
a decimal value cant get decimal(5,2) value.. it must be at least (10,2)

0

Commented:
What happens if you add a round to the statement....

CAST(round(count(distinct(integervalue),2)) AS decimal(5,2)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.