-1

I am having what I am sure an easy solution but I cant seem to find an answer via google. I am trying to calculate a defect rate with an sql server query and it is rounding my answer. The actual rate is 5.3 but it returns 5. How can I stop the query from rounding my answer?

code:

SELECT (sum(qty)/count(job)) as 'Defect Rate' 
 FROM (
SELECT count(*) as 'qty', job 
from tableA
WHERe CreationDate >= '2017-01-01 00:00'
group by Job) as t
Cheddar
  • 530
  • 4
  • 30
  • 1
    multiply by 1.0 the sum(qty) – QHarr Oct 25 '17 at 19:22
  • @QHarr that works, thanks so much! – Cheddar Oct 25 '17 at 19:25
  • 2
    SQL server calls this ***integer division***. Since qty and job are likely integers, then the result will also be integer. To resolve cast both to numeric values with specific desired decimals before dividing or multiply a value * 1.0 which will force the result to decimal. as QHarr has illustrated. Documented [here](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/divide-transact-sql). Do not use float. – xQbert Oct 25 '17 at 19:26
  • 1
    Cast your columns as numeric before your division. – Jacob H Oct 25 '17 at 19:26
  • except for the guy who down voted me – Cheddar Oct 25 '17 at 19:28
  • Possible duplicate of [Integer division in sql server](https://stackoverflow.com/questions/3443672/integer-division-in-sql-server) – xQbert Oct 25 '17 at 19:31
  • @xQbert I think to some extent, mine is working with columns instead of direct casting as a variable though. – Cheddar Oct 25 '17 at 19:32
  • Not sure what you mean but multiplying a value * 1.0 will work as well as casting. the advantage of casting is you control scale and precision – xQbert Oct 25 '17 at 20:37

1 Answers1

1

You have to use *1.0 like this :

SELECT (sum(qty)/count(job) * 1.0) as 'Defect Rate' FROM ( SELECT count(*) as 'qty', job from tableA WHERe CreationDate >= '2017-01-01 00:00' group by Job) as t

Amir H KH
  • 351
  • 2
  • 18
  • I had to multiply the sum qty, like Qharr suggested, I do suppose the other comments would have worked as effectively though. – Cheddar Oct 25 '17 at 19:27
  • That is not matter to multiply sum(qty) or count(job), the only important is the type of result of your division that 1.0 will change your type of result to float, do that and be sure it work's properly – Amir H KH Oct 25 '17 at 19:30