SQL - 总行数的百分比

问题描述

我有这个查询:

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.

希望这有帮助约翰

这篇关于SQL - 总行数的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！