zemirco
zemirco

Reputation: 16395

PostgreSQL Group values by category, count and calculate percentage

I've got a table moves.

uuid | tag | ...
-----| ----|----
abc  | 520 | ...
def  | 510 | ...
ghi  | 500 | ...
jkl  | 310 | ...
mno  | 200 | ...

The tag stands for the type of move. We are talking about moves in beach volleyball. The first number, e.g. the 5 from 520, is the category. "Service" in this case. In total I've got six categories:

  1. Attack (category 1, e.g. 100, 110 and 120)
  2. Block (category 2)
  3. Dig (category 3)
  4. Reception (category 4)
  5. Service (category 5)
  6. Setting (category 6)

The last number, i.e. the 20 from 520, is the outcome. "Win" in this case. Every category has 3 possible outcomes:

  1. Error (00)
  2. Zero (10)
  3. Win (20)

Here are the tags from the table above

  1. 520 - Service Win (which is an Ace)
  2. 510 - Service Zero
  3. 500 - Service Error
  4. 310 - Dig Zero
  5. 200 - Block Error

Here is what I'd like to get: Give me the count of errors, zeroes, wins for each category in absolute and relative values.

I tried the following

select *,
    (attack_error::float / attacks::float * 100) as attack_error_percentage,
    (attack_zero::float / attacks::float * 100) as attack_zero_percentage,
    (attack_win::float / attacks::float * 100) as attack_win_percentage
    from (
        select
            count(*) filter (where tag = 100) as attack_error,
            count(*) filter (where tag = 110) as attack_zero,
            count(*) filter (where tag = 120) as attack_win,
            count(*) filter (where tag = 100 or tag = 110 or tag = 120) as attacks
        from moves
        where match_uuid = 'd7eea231-a63d-4d73-b48f-5ca8541ec9cf' and set = 1
    )
as attack_stats

and got something like this

att_error | att_zero | att_win | total | att_error_% | att_zero_% | att_win_%
----------|----------|---------|-------|-------------|------------|----------
1         | 3        | 13      | 17    | 5.88        | 17.65      | 76.47

However it does not feel right as I'd have to repeat the queries again and again for all different categories with all their outcomes.

What I'd really like to get is something like this.

category | error | zero | win | total | error_% | zero_% | win_%
---------|-------|------|-----|-------|---------|--------|------
1        | 2     | 4    | 6   | 12    | 0.16    | 0.33   | 0.5
2        | 3     | 8    | 13  | 24    | 0.125   | 0.33   | 0.54
3        | ...   | ...  | ... | ...   | ...     | ...    | ...
4        | ...   | ...  | ... | ...   | ...     | ...    | ...
5        | ...   | ...  | ... | ...   | ...     | ...    | ...
6        | ...   | ...  | ... | ...   | ...     | ...    | ...

Any ideas?

Upvotes: 2

Views: 1577

Answers (1)

Parfait
Parfait

Reputation: 107652

Consider creating your category column conditionally with CASE statement and include it as a GROUP BY in the derived table aggregate query

select *,
    (error::float / total::float * 100) as error_percentage,
    (zero::float / total::float * 100) as zero_percentage,
    (win::float / total::float * 100) as win_percentage
    from (
        select
            case substring(tag::text, 1, 1)
                 when '1' then 'Attack' 
                 when '2' then 'Block' 
                 when '3' then 'Dig' 
                 when '4' then 'Reception' 
                 when '5' then 'Service' 
                 when '6' then 'Setting' 
            end as category,
            count(*) filter (where tag - round(tag/100, 0)*100 = 0) as error,
            count(*) filter (where tag - round(tag/100, 0)*100 = 10) as zero,
            count(*) filter (where tag - round(tag/100, 0)*100 = 20) as win,
            count(*) filter (where tag - round(tag/100, 0)*100 <= 20) as total
        from moves
        where match_uuid = 'd7eea231-a63d-4d73-b48f-5ca8541ec9cf' and set = 1
        group by
            case substring(tag::text, 1, 1)
                 when '1' then 'Attack' 
                 when '2' then 'Block' 
                 when '3' then 'Dig' 
                 when '4' then 'Reception' 
                 when '5' then 'Service' 
                 when '6' then 'Setting' 
            end
    )
as attack_stats

Upvotes: 1

Related Questions