Solved

# MS SQL + value to 2 decimal points

Posted on 2006-05-13
249 Views
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
0
Question by:dkilby

LVL 6

Expert Comment

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

Author Comment

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

Author Comment

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

LVL 6

Expert Comment

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

LVL 6

Expert Comment

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

LVL 6

Expert Comment

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

LVL 2

Expert Comment

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

Author Comment

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

LVL 2

Accepted Solution

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

LVL 2

Expert Comment

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

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

## Featured Post

### Suggested Solutions

I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…