Compute ratio of group sizes using SQL

Question

Consider a simple group by query:

select foo, count(*)
    from mytable where bar=10 
group by foo

This returns a table that has the following form:

foo | count
----+------
a   | 100
b   | 200
c   | 300

My goal is to get, using a single query the following table:

foo | count | ratio
----+-------+-------
a   | 200   | 18.2
b   | 300   | 27.3
c   | 600   | 54.5

In practice, I have more possible values of foo thus answers like those in here are not helpful. Furthermore, not that the ratio is rounded and multiplied by 100.

What is the best practice to do this?

Answer 1

Sounds like you want something like this:

select foo, count(*),
       count(*) * 100.0 / sum(count(*)) over () as ratio
from mytable
where bar = 10 
group by foo;

This does not guarantee that the value adds up to exactly 100% when rounded. That is a much tricker problem, usually better handled at the application layer. This does produce the "correct" answer, using floating point numbers.