SQL: Group by count(*) as pct of total table row count?

Since PostgreSQL supports window functions, you could do something like this.

Since PostgreSQL supports window functions, you could 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.

This type of query has a lot of potential applications, thanks for the pointer! – Gabriel Magana Nov 4 at 15:22 Well, if there are few values of categorynum relative to the number of rows in tbl, then most of the work will be done in the subquery. If the proportion of distinct categorynums relative to the number of rows in tbl is relatively high, then the sum may slow you down too much.

I'd check the query plan via an explain for both queries. And you're very welcome. :) – Jack Maney Nov 4 at 16:19.

You can also do the count(*) on the table as a separate query and then join that with your original query in the FROM portion of your SELECT statement. That should be quicker 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.

Thanks, that was the only option I had before... The thing is this table is always getting data added to it, and also I would much rather have a single query than have to run two queries and then type the numbers into a calculator/spreadsheet to get the percentages. – Gabriel Magana Nov 4 at 20:20 I think the answerer had a single query in mind... I will write a suggestion into the answer where it will be readable. – araqnid Nov 5 at 0:05 although the example I just added isn't any more efficient than your example in the question, they both result in two full scans of the table.

Ah well, these things are only really learned by trying them. – araqnid Nov 5 at 0:10 Yes, in Postgres maybe they do full table scans, but I stated in my question that count(*) in my DB (which is Postgres syntax compatible) it does not cause a full table scan, so that is not a factor here. – Gabriel Magana Nov 6 at 15:46.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.