CODEWITHSUNDEEP
sqlsql-server
user20929302
user20929302

Reputation: 403

SQL Dividing two SUM() Functions

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

Upvotes: 3

Views: 32191

Answers (2)

Dataichou
Dataichou

Reputation: 317

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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))

Upvotes: 5

Related Questions