CODEWITHSUNDEEP
phpmysql
dennis ramli
dennis ramli

Reputation: 73

make standings table soccer league

I have two tables .. first table

   id_klub    || nama_klub
      01      || manchester
      02      || chelsea
      03      || liverpool
      04      || arsenal

second table

 id_skor  || Date        || Home  || Away || skor_home||  skor_away
 0001     ||  12/12/12   ||  01   || 02   ||     3     ||     2 
 0002     ||  13/12/12   ||  02   || 03   ||     2     ||     2
 0003     ||  14/12/12   ||  04   || 03   ||     1     ||     3
 0004     ||  15/12/12   ||  04   || 01   ||     3     ||     1

that in second table home and away means id_club, example :

when home = 01 and away = 02 that means manchester vs chealsea, and my question is, how i can make standings table from that 2 table?

club_name   ||game ||win    ||lose  ||draw  ||point
chelsea     || 2   ||0      || 1    || 1    || 1

with logic
win = point +3;
lose = point +0;
draw = point +1;

i have tried with this query

SELECT nama_klub,

count(case when skor_home > skor_away then 1 end) wins, 
count(case when skor_home < skor_away then 1 end) lose, 
count(case when skor_home = skor_away then 1 end) draw,

sum(
          case when skor_home > skor_away then 3 else 0 end 
        + case when skor_home = skor_away then 1 else 0 end
    ) score

FROM klub INNER JOIN game ON klub.id_klub = game.home

GROUP BY id_skor

Upvotes: 1

Views: 819

Answers (1)

Shadow
Shadow

Reputation: 34231

You need to join game table twice on the klub table , once on home, and once on the away fields to get all games in which a team participated in.

Then you need to add up the conditional counts from the 2 tables to get the expected output, sg along the below code.

select nama_klub,
       count(g1.home) + count(g2.away) as 'game',
       count(if(g1.skor_home>g1.skor_away,1,null)) + count(if(g2.skor_home<g2.skor_away,1,null)) as win,
       count(if(g1.skor_home=g1.skor_away,1,null)) + count(if(g2.skor_home=g2.skor_away,1,null)) as draw,
       count(if(g1.skor_home<g1.skor_away,1,null)) + count(if(g2.skor_home>g2.skor_away,1,null)) as loss,
       (count(if(g1.skor_home>g1.skor_away,1,null)) + count(if(g2.skor_home<g2.skor_away,1,null))) * 3 + (count(if(g1.skor_home=g1.skor_away,1,null)) + count(if(g2.skor_home=g2.skor_away,1,null))) as score
from klub k
left join game g1 on k.id_klub=g1.home
left join game g2 on k.id_klub=g2.away
group by k.id_klub, k.nama_klub

Upvotes: 2

Related Questions