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?

Contortionist

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.

Mercantilism

Your solution here above is just wrong! I did cut & paste your code (even if not really needed - you just have to read it) and produces wrong results. Did you try to run it? – Arris

That's work completely as expected. @Arris would you like to explain what you saw wrong here?? – Sebiferous

Answer 2

Here's a working example that drags its own data along - which you can modify to your individual needs:

    SQL>WITH mytable(
    ...>foo , counter
    ...>) AS (
    ...>          SELECT 'a',200
    ...>UNION ALL SELECT 'b',300
    ...>UNION ALL SELECT 'c',600
    ...>)
    ...>SELECT
    ...>  foo
    ...>, counter
    ...>, (counter * 100.0 / SUM(counter) OVER ())::NUMERIC(3,1) AS ratio
    ...>FROM mytable
    ...>;
    foo|counter             |ratio
    a  |                 200| 18.2
    b  |                 300| 27.3
    c  |                 600| 54.5
    select succeeded; 3 rows fetched

Happy playing ... marcothesane

Carnage

OP said "In practice, I have more possible values of foo thus answers like those in here are not helpful." – Picoline