Dror Dror - 1 year ago 63
SQL Question

Compute ratio of group sizes using SQL

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
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download