dennis ramli dennis ramli - 1 month ago 6
MySQL Question

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

Answer

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