Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wrote a football table program for my friends and they would really like to have some more statistics in it. But I'm stumbling about getting total wins and lose for the red and black team (W_S = win black; L_S = lose black) (W_R = win red; L_R = lose red)

Here is my schema and select:

http://sqlfiddle.com/#!2/814a35/1/0

As you can see, W_S and L_S are always zero and W_R and L_R are wrong populated.

(schwarz = black; rot = red; tore = goals)

Thanks in advance!

edit

whole SELECT query

SELECT 
        `SpielerID` AS `id`, 
        `s`.`name` AS `name`, 
        COALESCE( (Sum(W_R) / NULLIF((Sum(W_R)+Sum(W_S))+(Sum(L_R)+Sum(L_S)), 0)), Sum(W_R) ) as 'rot_siegeRatio', 
        COALESCE( (Sum(W_S) / NULLIF((Sum(W_R)+Sum(W_S))+(Sum(L_R)+Sum(L_S)), 0)), Sum(W_S) ) as 'schwarz_siegeRatio', 
        Sum(W_R) as 'siegeCount', Sum(L_R) as 'niederlagenCount', (Sum(W_R)+Sum(L_R)) as 'spieleCount', 
        COALESCE( (Sum(W_R) / NULLIF((Sum(W_R))+(Sum(L_R)), 0)), Sum(W_R) ) as 'siegeRatio', Sum(Tore) as 'toreCount',
        Sum(Gegentore) as 'gegentoreCount',
        COALESCE( (Sum(Tore) / NULLIF(Sum(Gegentore), 0)), Sum(Tore) ) as 'toreRatio'
FROM 
        ( SELECT rot_spieler1 SpielerID, datetime,
          IF (rot_tore > schwarz_tore,1,0) W_R,
          IF (rot_tore < schwarz_tore,1,0) L_R,
          0 W_S,
          0 L_S,
          rot_tore Tore,
          schwarz_tore Gegentore
          FROM `kicker_spiele`
          WHERE datetime IS NOT NULL

          UNION ALL
          SELECT rot_spieler2 SpielerID, datetime,
          IF (rot_tore > schwarz_tore,1,0) W_R,
          IF (rot_tore < schwarz_tore,1,0) L_R,
          0 W_S,
          0 L_S,
          rot_tore Tore,
          schwarz_tore Gegentore
          FROM `kicker_spiele`
          WHERE datetime IS NOT NULL

          UNION ALL
          SELECT schwarz_spieler1 SpielerID, datetime,
          IF (schwarz_tore > rot_tore,1,0) W_S,
          IF (schwarz_tore < rot_tore,1,0) L_S,
          0 W_R,
          0 L_R,
          schwarz_tore Tore,
          rot_tore Gegentore
          FROM `kicker_spiele`
          WHERE datetime IS NOT NULL

          UNION ALL
          SELECT schwarz_spieler2 SpielerID, datetime,
          IF (schwarz_tore > rot_tore,1,0) W_S,
          IF (schwarz_tore < rot_tore,1,0) L_S,
          0 W_R,
          0 L_R,
          schwarz_tore Tore,
          rot_tore Gegentore
          FROM `kicker_spiele`
          WHERE datetime IS NOT NULL
         ) as ERG
LEFT JOIN `kicker_spieler` `s` ON ERG.SpielerID = s.id
GROUP by ERG.SpielerID
share|improve this question
    
Works! stackoverflow.com/a/21450739/3248975 –  hicksi Jan 30 at 19:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.