CODEWITHSUNDEEP
sqlvertica
Dror
Dror

Reputation: 13051

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

Upvotes: 6

Views: 5496

Answers (2)

marcothesane
marcothesane

Reputation: 6741

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

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 7

Related Questions