SQL - percentage of total rows

Question

I have this query:

SELECT 
Count(*) as Cnt, 
Category
FROM [MyDb].[dbo].[MyTable]
group by Category
order by 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).

How can I do that?

Answer 1

As a note, you can actually do this with one query by using window functions:

SELECT Count(*) as Cnt, Category,
       cast(Count(*) as float) / sum(count(*)) over () as ThirdColumn
FROM [MyDb].[dbo].[MyTable]
group by Category
order by Cnt

Answer 2

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

or with a variable:

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

I've cast count(*) as real in both examples to avoid integer-division type issues.

Hope this helps John