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?

score 6 · Accepted Answer · answered Aug 23 '16 at 06:22

6

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.

answered Aug 23 '16 at 06:22

Gordon Linoff

1,242,037
58
646
786

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? – mauro Sep 03 '16 at 20:19
2

That's work completely as expected. @mauro would you like to explain what you saw wrong here?? – User1234 Dec 16 '19 at 14:56

score -1 · Answer 2 · answered Sep 03 '16 at 14:09

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

OP said "In practice, I have more possible values of foo thus answers like those in here are not helpful." — Red, Jan 25 '21 at 12:26

Compute ratio of group sizes using SQL

2 Answers2