问题描述
我有这个查询:
SELECT Count(*) as Cnt, Category FROM [MyDb].[dbo].[MyTable] group by Category order by Cnt
它为我提供了每个 Category 中的行数.现在我想添加第三列,它会给我 Cnt/(此表中的总行数).
It gives me count of rows in each Category. Now I would like to add a third column that would give me Cnt / (total rows in this table).
我该怎么做?
推荐答案
你可以用子查询来做到:
you could do it with a subquery:
SELECT Count(*) as Cnt, Category, (Cast(Count(*) as real) / cast((SELECT Count(*) FROM [MyDb].[dbo].[MyTable]) as real)) AS [Percentage] FROM [MyDb].[dbo].[MyTable] group by Category order by Cnt
或使用变量:
declare @total real; select @total = count(*) from [MyDb].[dbo].[MyTable]; SELECT Count(*) as Cnt, Category, (Cast(Count(*) as real) / @total) AS [Percentage] FROM [MyDb].[dbo].[MyTable] group by Category order by Cnt
我在两个示例中都将 count(*) 转换为实数以避免整数除法类型问题.
I've cast count(*) as real in both examples to avoid integer-division type issues.
希望这有帮助约翰