Round formats

mysql> select eventId, formatId, case when value1 != 0 then 1 else 0 end + case when value2 != 0 then 1 else 0 end + case when value3 != 0 then 1 else 0 end + case when value4 != 0 then 1 else 0 end + case when value5 != 0 then 1 else 0 end as numSolves, count(distinct competitionId, roundTypeId) as numRounds from Results group by eventId, formatId, numSolves order by eventId, numRounds desc;
+---------+----------+-----------+-----------+
| eventId | formatId | numSolves | numRounds |
+---------+----------+-----------+-----------+
| 222     | a        |         5 |     10080 |
| 222     | a        |         2 |       637 |
| 222     | 2        |         2 |        20 |
| 222     | 3        |         3 |         8 |
| 222     | a        |         1 |         4 |
| 222     | 1        |         1 |         3 |
| 222     | a        |         3 |         2 |
| 333     | a        |         5 |     14884 |
| 333     | a        |         2 |       211 |
| 333     | 3        |         3 |        62 |
| 333     | 2        |         2 |        20 |
| 333     | 1        |         1 |         7 |
| 333     | a        |         3 |         6 |
| 333     | 3        |         1 |         1 |
| 333     | a        |         1 |         1 |
| 333bf   | 3        |         3 |      5089 |
| 333bf   | 3        |         2 |       332 |
| 333bf   | 2        |         2 |       165 |
| 333bf   | 1        |         1 |        13 |
| 333bf   | 3        |         1 |        10 |
| 333bf   | 2        |         1 |         1 |
| 333fm   | 1        |         1 |      1002 |
| 333fm   | m        |         3 |       574 |
| 333fm   | 2        |         2 |       114 |
| 333fm   | 3        |         3 |        35 |
| 333fm   | m        |         1 |         3 |
| 333fm   | 2        |         1 |         1 |
| 333ft   | m        |         3 |      1107 |
| 333ft   | m        |         1 |       534 |
| 333ft   | a        |         5 |       306 |
| 333ft   | a        |         2 |       181 |
| 333ft   | 1        |         1 |        22 |
| 333ft   | 3        |         3 |        17 |
| 333ft   | 2        |         2 |        12 |
| 333ft   | m        |         2 |         3 |
| 333ft   | 3        |         1 |         2 |
| 333mbf  | 1        |         1 |      1027 |
| 333mbf  | 2        |         2 |       434 |
| 333mbf  | 3        |         3 |       138 |
| 333mbf  | 2        |         1 |         4 |
| 333mbf  | 3        |         2 |         2 |
| 333mbf  | 3        |         1 |         1 |
| 333mbo  | 1        |         1 |        37 |
| 333mbo  | 2        |         2 |        21 |
| 333mbo  | 3        |         3 |         2 |
| 333oh   | a        |         5 |      7961 |
| 333oh   | a        |         2 |      2690 |
| 333oh   | 2        |         2 |        45 |
| 333oh   | a        |         3 |        19 |
| 333oh   | m        |         3 |        19 |
| 333oh   | 3        |         3 |        17 |
| 333oh   | a        |         1 |        13 |
| 333oh   | 1        |         1 |         1 |
| 444     | a        |         5 |      7859 |
| 444     | a        |         2 |      3454 |
| 444     | 2        |         2 |        49 |
| 444     | m        |         3 |        32 |
| 444     | a        |         1 |        29 |
| 444     | 3        |         3 |        21 |
| 444     | a        |         3 |        18 |
| 444     | m        |         1 |         1 |
| 444     | 1        |         1 |         1 |
| 444bf   | 3        |         3 |      1035 |
| 444bf   | 2        |         2 |       238 |
| 444bf   | 1        |         1 |        67 |
| 444bf   | 3        |         1 |         6 |
| 444bf   | 3        |         2 |         2 |
| 444bf   | 2        |         1 |         1 |
| 555     | a        |         5 |      5029 |
| 555     | a        |         2 |      2763 |
| 555     | 2        |         2 |        39 |
| 555     | a        |         1 |        36 |
| 555     | 3        |         3 |        25 |
| 555     | m        |         3 |        21 |
| 555     | a        |         3 |        16 |
| 555     | 3        |         1 |         3 |
| 555     | 3        |         2 |         2 |
| 555     | m        |         1 |         1 |
| 555     | 1        |         1 |         1 |
| 555     | 2        |         1 |         1 |
| 555bf   | 3        |         3 |       786 |
| 555bf   | 2        |         2 |       145 |
| 555bf   | 1        |         1 |        67 |
| 555bf   | 3        |         1 |         3 |
| 555bf   | 3        |         2 |         2 |
| 555bf   | 2        |         1 |         1 |
| 666     | m        |         3 |      2194 |
| 666     | m        |         1 |      1403 |
| 666     | m        |         2 |         7 |
| 666     | 2        |         2 |         2 |
| 666     | 3        |         3 |         1 |
| 666     | 1        |         1 |         1 |
| 777     | m        |         3 |      2110 |
| 777     | m        |         1 |      1398 |
| 777     | m        |         2 |         9 |
| 777     | 2        |         2 |         5 |
| 777     | 1        |         1 |         2 |
| 777     | 2        |         1 |         1 |
| 777     | a        |         1 |         1 |
| clock   | a        |         5 |      2305 |
| clock   | a        |         2 |       988 |
| clock   | m        |         3 |        71 |
| clock   | 2        |         2 |         4 |
| clock   | 3        |         3 |         3 |
| clock   | m        |         2 |         1 |
| clock   | a        |         1 |         1 |
| clock   | 1        |         1 |         1 |
| clock   | a        |         3 |         1 |
| clock   | 3        |         1 |         1 |
| clock   | m        |         1 |         1 |
| magic   | a        |         5 |      1017 |
| magic   | 3        |         3 |         7 |
| magic   | 1        |         1 |         1 |
| magic   | a        |         1 |         1 |
| minx    | a        |         5 |      3155 |
| minx    | a        |         2 |      1971 |
| minx    | m        |         3 |       178 |
| minx    | m        |         1 |        73 |
| minx    | a        |         1 |        21 |
| minx    | 1        |         1 |        15 |
| minx    | 3        |         3 |        14 |
| minx    | a        |         3 |         7 |
| minx    | m        |         2 |         5 |
| minx    | 2        |         2 |         4 |
| minx    | 3        |         1 |         3 |
| mmagic  | a        |         5 |       775 |
| mmagic  | 3        |         3 |         3 |
| mmagic  | 1        |         1 |         1 |
| mmagic  | 2        |         2 |         1 |
| mmagic  | a        |         2 |         1 |
| pyram   | a        |         5 |      7066 |
| pyram   | a        |         2 |       970 |
| pyram   | 2        |         2 |         5 |
| pyram   | a        |         3 |         5 |
| pyram   | 3        |         3 |         2 |
| pyram   | 1        |         1 |         1 |
| skewb   | a        |         5 |      4284 |
| skewb   | a        |         2 |       814 |
| skewb   | 3        |         3 |         1 |
| sq1     | a        |         5 |      3257 |
| sq1     | a        |         2 |      1552 |
| sq1     | m        |         3 |        99 |
| sq1     | m        |         1 |        17 |
| sq1     | a        |         1 |        10 |
| sq1     | 2        |         2 |         5 |
| sq1     | m        |         2 |         2 |
| sq1     | a        |         3 |         2 |
| sq1     | 1        |         1 |         2 |
| sq1     | 3        |         3 |         1 |
+---------+----------+-----------+-----------+
149 rows in set (5.82 sec)