MS SQL + value to 2 decimal points

I am trying to get a value to 2 decimal points but can't get it to work,

this is what i have but no matter where i put convert(decimal(18,2),) it doesn't make a difference.

here is what i have

CASE WHEN COUNT(CUSTOMER_LOG_ID) = 0 THEN 0 ELSE
(CONVERT(decimal(18,2),SUM(case when meet = 1 then 1 else 0 end)) / CONVERT(decimal(18,2),COUNT(CUSTOMER_LOG_ID))) * 100 END  as TEST

thanks
dkilbyAsked:
Who is Participating?
 
OmnibuzzCommented:
before the division, multiply the numerator or denomenator with 1.0, so that it doesn't do an autoconversion to int..
Somthing like this..

select convert(decimal(18,2),(1*1.0 / 3)) as 'TEST'  
0
 
itdrmsCommented:
Are you getting an error message, or just not your results.
I had the same problem yesterday, I thought BOL was misleading.  I had to define decimal(4,2) to hold .25.
0
 
dkilbyAuthor Commented:
no error message - just not getting the result - there are lots of numbers after the decimal place, so it doesn't seem to do anything.

I will try decimal(4,2)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dkilbyAuthor Commented:
tried decimal(4,2) and depending on where i but it i get either an error, or just zeros

the error is

Arithmetic overflow error converting numeric to data type numeric.
0
 
itdrmsCommented:
I'm sorry, I didn't mean for you to define the size as 4,2 -- just making the point that I only had 3 places in my result and had to extend the size definition.
0
 
itdrmsCommented:
okay so your getting more than 2 past the decimal -- got it now -- might be that the first 0 in the if is not explicitly defined -- I'm trying to test.
0
 
itdrmsCommented:
I took your syntax and got the same problem -- too many digits after the decimal.

I took out all the conversions (in my modified version) and changed to one just around the entire calculation and it worked

select
convert(decimal(18,2),SUM(case when test1 > 5  then 1 else 0 end) / COUNT(test1) *100)
FROM test

so in your example, it must be the final (* 100) that is throwing it off.
0
 
OmnibuzzCommented:
didn't check it, so might have a few braces missing or extra :) But check this and let me know if this is what you wanted?

CONVERT((DECIMAL(18,2),CASE WHEN COUNT(CUSTOMER_LOG_ID) = 0 THEN 0 ELSE
(SUM(case when meet = 1 then 1 else 0 end)*100.00) / COUNT(CUSTOMER_LOG_ID) END)  as TEST
0
 
dkilbyAuthor Commented:
ok tried several different combinations

select convert(decimal(18,2),(1 / 3)  * 100) as 'TEST'  

returned .00

select (1 /convert(decimal(18,2), 3))  * 100 as 'TEST'

returned 33.3333333333333333300

select convert(decimal(18,2),(1 / 3))  as 'TEST'  -- took the 100 out

returned .00

select convert(decimal(18,2),1 / 3)  as 'TEST'

returned .00

select convert(decimal(18,2),1) /convert(decimal(18,2), 3)  as 'TEST'

.33333333333333333333


think that is all combinations possible

0
 
OmnibuzzCommented:
of if you want to stick to standards, use this :)

select convert(decimal(18,2),(cast(1 as decimal) / 3)) as 'TEST'  
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.

All Courses

From novice to tech pro — start learning today.