查询:
select
(
SELECT COUNT(*)
FROM Sending s
WHERE (ConfID = 1) AND (Status = 1)
)
/
(
(
SELECT COUNT(*)
FROM Numbers
WHERE (ConfID = 1)
)
/
100
)
我想计算百分比。例如,if query
SELECT COUNT(*)
FROM Numbers
WHERE (ConfID = 1)
给出100和另一个
SELECT COUNT(*)
FROM Sending s
WHERE (ConfID = 1) AND (Status = 1)
results 50结果查询应该返回50,这意味着50%。但是第一次查询结果为2,第二次查询返回10000,结果查询返回0。我想我应该定义返回浮点数之类的。
谢谢!
试着用100.0
代替100
。这将强制转换为浮点数据类型:
select
(
SELECT COUNT(*)
FROM Sending s
WHERE (ConfID = 1) AND (Status = 1)
)
/
(
(
SELECT COUNT(*)
FROM Numbers
WHERE (ConfID = 1)
)
/
100.0
)
尝试:
SELECT SUM(CASE status WHEN 1 THEN 100 ELSE 0 END) / COUNT(*)
FROM numbers
WHERE confid = 1