SQL 划分两个 SUM() 函数

Question

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

我正在尝试使用 sql 创建上表。结果并不是我所期望的。 结果如下

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

sql 代码：

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

只是想知道为什么我的(((SUM(CountFail(/SUM(CountWin(( * 100没有返回正确的值

Answer 1

您的数据库可能正在执行整数除法。 只需按以下方式进行计算：

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;

笔记：

• 您的where子句正在使用getdate()上的时间。 您更可能想要：date > dateadd(day, -7, cast(getdate() as date)).
• NULLIF()防止除以 0。
• 你的计算是比例，而不是百分比。 也许你打算：SUM(CountFail) * 100.0 / NULLIF(SUM(CountWin + CountFail))

Answer 2

你必须把它投射到浮动

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