Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

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
0
dkilby
Asked:
dkilby
  • 4
  • 3
  • 3
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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:
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
 
OmnibuzzCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now