mardi 22 mars 2016

MySQL rank across multiple data sets for a scoreboard

I'm trying to build a scoreboard for a multi event competition.

I have a table with data like this:

id competitor  wod score
1  Noah Ohlsen 01  350
2  Noah Ohlsen 02  430
3  Noah Ohlsen 03  140
4  Noah Ohlsen 04  314

I have a SQL query that gets med rank per "wod":

SELECT competitor, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM wodcomp.scoring WHERE wod='01' )
) AS wod01,
FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM wodcomp.scoring WHERE wod='02' )
) AS wod02
,
FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM wodcomp.scoring WHERE wod='03' )
) AS wod03
,
FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM wodcomp.scoring WHERE wod='04' )
) AS wod04
FROM wodcomp.scoring competitor;

The result is:

Competitor  wod01 wod02 wod03 wod04
Noah Ohlsen 1     0     0     0
Noah Ohlsen 0     1     0     0
Noah Ohlsen 0     0     1     0
Noah Ohlsen 0     0     0     1

I would like it to be one combined row:

Competitor  wod01 wod02 wod03 wod04
Noah Ohlsen 1     1     1     1

Or even with a total across the events based on the combined rank in the different wod, like this:

Competitor  wod01 wod02 wod03 wod04 wodtotal
Noah Ohlsen 1     1     1     1     1



MySQL rank across multiple data sets for a scoreboard

Aucun commentaire:

Enregistrer un commentaire