1
Name  | CountWin | CountFail | Failure% |
---------------------------------
TypeA |   100    |   50      |    50    |
TypeB |   100    |   5       |     5    |
TypeC |   100    |   100     |   100    |
TypeD |   100    |    0      |     0    |

I am trying to create the above table with sql. The results are not what I expect though. The results are below

 Name  | CountWin | CountFail | Failure% |
---------------------------------
TypeA |   100    |   50      |     0    |
TypeB |   100    |   5       |     0    |
TypeC |   100    |   100     |     0    |
TypeD |   100    |    0      |     0    |

The sql code:

INSERT INTO #my_temp_table
  select type, date, CountWin, CountFail from myTable

select type, SUM(CountWin) as CountWin, SUM(CountFail) as CountFail, (((SUM(CountFail) / SUM(CountWin)) * 100) as Failure%
FROM #my_temp_table
WHERE date > DATEADD(day, -7, getdate())
GROUP BY type, date

Just wondering why my (((SUM(CountFail) / SUM(CountWin)) * 100 is not returning proper values

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user20929302
  • 383
  • 1
  • 4
  • 14
  • 1
    Possible duplicate of [How to get a float result by dividing two integer values?](https://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values) – Pac0 Jun 06 '18 at 13:22
  • I agree with the duplicate vote, but I'd use [**this answer from the question**](https://stackoverflow.com/a/11719105/6167855) instead of the accepted one. No need to use `CAST` or `CONVERT` – S3S Jun 06 '18 at 13:24

2 Answers2

4

Your database is probably doing integer division. Simply do the calculation as:

select type, date, SUM(CountWin) as CountWin, SUM(CountFail) as CountFail,
      SUM(CountFail) * 100.0 / NULLIF(SUM(CountWin), 0) as Failure_percent
FROM #my_temp_table
WHERE date > DATEADD(day, -7, getdate())
GROUP BY type, date;

Notes:

  • Your where clause is using the time on getdate(). More likely you want: date > dateadd(day, -7, cast(getdate() as date)).
  • The NULLIF() prevents division by 0.
  • Your calculation is a ration, not a percent. Perhaps you intend: SUM(CountFail) * 100.0 / NULLIF(SUM(CountWin + CountFail))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, worked perfectly. Wondering why did you do SUM(CountFail) * 100 first? I know it still returns a larger number, but just wondering why the divide wasn't first in your example – user20929302 Jun 06 '18 at 13:25
  • 1
    @user20929302 . . . If the division is first, then SQL Server does an integer divide -- and produces an integer (usually 0). With the multiplication first the value is converted from an integer and the division behaves as expected. – Gordon Linoff Jun 07 '18 at 02:27
1

You have to cast it to float

INSERT INTO #my_temp_table
select type, date, CountWin, CountFail from myTable

select type, SUM(CountWin) as CountWin, SUM(CountFail) as 
CountFail, (((SUM(CountFail) / CAST(SUM(CountWin) as float)) * 100) as 
Failure%
FROM #my_temp_table
WHERE date > DATEADD(day, -7, getdate())
GROUP BY type, date
Stevoisiak
  • 23,794
  • 27
  • 122
  • 225
Dataichou
  • 317
  • 1
  • 6