SQL: group by account (*) as pct of the total number of rows in the table

Question

I spent a lot of time looking for this, please let me know if duplicating.

I need to write a grouped query that returns categories of records counting each category type. Something like that:

select categorynum, count(*) from tbl group by categorynum; 

So far so good. Now I need to determine how many percent of the total amount of each category takes. The best I came up with is that I don't like it, it feels dirty:

 select categorynum, count(*), count(*)/(select count(*) from tbl) from tbl group by categorynum; 

It works, but it really pushes me to do it that way. The database I use is compatible with Postgres, and count(*) in the table is very fast, so there aren’t a lot of hits to do count(*) in the table, although I would like to write better SQL, if at all possible.

So is there a better way to write this? This is a situation that I often encounter, so I would like to write my queries correctly.

Answer 1

Since PostgreSQL supports window functions, you can do something like this:

 select categorynum,count,100*count/(sum(count) over ())::numeric as count_pct from( select categorynum,count(1) from tbl group by categorynum )a; 

Answer 2

You can also make an invoice (*) in the table as a separate query, and then attach it to the original query in the FROM part of your SELECT statement. This should be faster than putting it in the SELECT part.

 select categorynum, categorycount, total from (select categorynum, count(*) as categorycount from tbl group by categorynum) categories, (select count(*) as total from tbl) totals