SQL-总行数百分比

Question

我有这个查询：

SELECT 
Count(*) as Cnt, 
Category
FROM [MyDb].[dbo].[MyTable]
group by Category
order by Cnt

它给了我每一行的计数Category。现在，我想添加第三列，这将给我带来帮助Cnt / (total rows in this table)。

我怎样才能做到这一点？

Answer 1

您可以使用子查询来做到这一点：

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（*）转换为实数，以避免整数除法类型问题。

希望这对约翰有帮助