Reputation: 16395
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:
100
, 110
and 120
)The last number, i.e. the 20 from 520, is the outcome. "Win" in this case. Every category has 3 possible outcomes:
00
)10
)20
)Here are the tags from the table above
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
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