I had the same problem yesterday, I thought BOL was misleading. I had to define decimal(4,2) to hold .25.

Solved

Posted on 2006-05-13

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

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

thanks

10 Comments

I had the same problem yesterday, I thought BOL was misleading. I had to define decimal(4,2) to hold .25.

I will try decimal(4,2)

the error is

Arithmetic overflow error converting numeric to data type numeric.

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(

FROM test

so in your example, it must be the final (* 100) that is throwing it off.

CONVERT((DECIMAL(18,2),CAS

(SUM(case when meet = 1 then 1 else 0 end)*100.00) / COUNT(CUSTOMER_LOG_ID) END) as TEST

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

Title | # Comments | Views | Activity |
---|---|---|---|

How to select/populate table with most current records | 2 | 49 | |

Delete from table | 6 | 26 | |

Windows Management Instrument | 6 | 8 | |

sQL pivot | 9 | 10 |

Via a live example, show how to setup several different housekeeping processes for a SQL Server.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**24** Experts available now in Live!