SQL 除以二 Count()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1127558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Divide by Two Count()
提问by Jefe
I have the following query, which is trying to figure out the percentage of a certain product compared to the total number of products. IE: [Product Count] / [Total Products] = Percent
我有以下查询，它试图找出某个产品占产品总数的百分比。IE：[产品数量] / [产品总数] = 百分比
;WITH totalCount AS(
SELECT
CAST(COUNT(id) as Integer)as totalCount
FROM TABLE_NAME
)
SELECT
((CAST(COUNT(DISTINCT id) as Integer)/(SELECT * FROM totalCount))*100) as 'Percent'
FROM TABLE_NAME
However, the percent column always returns "0" unless there is only one record. In addition, is there a way to add the totalCount and Select query into one?
但是，除非只有一条记录，否则百分比列始终返回“0”。另外，有没有办法将 totalCount 和 Select 查询合二为一？
Basically, how do you divide two Count() fields?
基本上，你如何划分两个 Count() 字段？
采纳答案by n8wrl
Cast your total count as a number besides integer (DECIMAL?) - the math rounds off.
将您的总数作为整数（DECIMAL？）之外的数字 - 数学四舍五入。
回答by Remus Rusanu
Cast as something with decimal precision, not Integer. A float or real.
转换为具有小数精度的东西，而不是整数。浮动或真实。
select cast(distinctCount as real)/cast(totalCount as real) * 100.00
, distinctCount
, totalCount
from (
select count(distinct id) as distinctCount
, count(id) as totalCount
from Table) as aggregatedTable
回答by Philippe Leybaert
Shouldn't that be:
不应该是：
;WITH totalCount AS(
SELECT
CAST(COUNT(id) as Integer)as totalCount
FROM TABLE_NAME
)
SELECT
((CAST(COUNT(DISTINCT id) as Integer)*100/(SELECT count(*) FROM totalCount))) as 'Percent'
FROM TABLE_NAME
Note the SELECT COUNT(*). Also, you should multiply before you divide, otherwise you'll always get zero
注意 SELECT COUNT(*)。另外，你应该在除法之前乘以，否则你总是会得到零
