SQLite 子查询数学返回错误值

Question

我正在查询数据库以计算各州的来电百分比。第一个查询正确提取数据，但当我尝试进行所需的数学运算时，它没有返回正确的值。

SELECT state, count(call) as Calls, (Select count(call) FROM test) as TotalCalls
FROM test
WHERE state IS NOT NULL
GROUP BY state
ORDER state ASC;

State    Calls    TotalCalls
-----    -----    ----------
Alabama   416       32149
Alaska     31       32149
Arizona   685       32149
Arkansas  216       32149

现在更改查询以进行数学计算，它返回 0。前三个的答案应该是 1.29、0.09、2.13

SELECT state, ROUND((count(call)/(Select count(call) FROM test) * 100.00), 2) AS StatePct
FROM test
WHERE state IS NOT NULL
GROUP BY state
ORDER BY state ASC;

State     StatePct
-----     --------
Alabama     0.0
Alaska      0.0
Arizona     0.0
Arkansas    0.0
California  0.0
Colorado    0.0

认为我的 SQL 没有格式错误，我逆向计算并且它有效!

SELECT state, ROUND(((Select count(call) FROM test)/count(call) * 100.00), 2) AS StatePct
FROM test
WHERE state IS NOT NULL
GROUP BY state
ORDER BY state ASC;

State      StatePct
-----      --------
Alabama     7700.0
Alaska    103700.0
Arizona     4600.0
Arkansas   14800.0
California   700.0
Colorado    3700.0
Connecticut 5500.0

虽然我认为这与 OSX 上的 SQLite3 无关。

Answer 1

啊关键!将分子转换为 FLOAT，现在它可以工作了。谢谢。

SELECT state, ROUND((CAST(count(call) AS FLOAT)/(Select count(call) FROM test) * 100.00), 2) AS StatePct
FROM test
WHERE state IS NOT NULL
GROUP BY state
ORDER BY state ASC;