Cacher is the code snippet organizer for pro developers

select 
    team, 
    count(*) played, 
    count(case when goalsfor > goalsagainst then 1 end) wins, 
    count(case when goalsagainst> goalsfor then 1 end) lost, 
    count(case when goalsfor = goalsagainst then 1 end) draws, 
    sum(goalsfor) goalsfor, 
    sum(goalsagainst) goalsagainst, 
    sum(goalsfor) - sum(goalsagainst) goal_diff,
    sum(
          case when goalsfor > goalsagainst then 3 else 0 end 
        + case when goalsfor = goalsagainst then 1 else 0 end
    ) score 
from (
    select hometeam team, goalsfor, goalsagainst from scores 
  union all
    select awayteam, goalsagainst, goalsfor from scores
) a 
group by team
order by score desc, goal_diff desc;

  SELECT
    team_id                                             team_id,
    t.name                                              team_name,
    t.country                                           country,
    count(*)                                            matches,
    SUM(scored)                                         scored_total,
    SUM(conceided)                                      conceided_total,
    count(CASE WHEN scored > conceided
      THEN 1 END)                                       wins,
    count(CASE WHEN scored = conceided
      THEN 1 END)                                       draws,
    count(CASE WHEN scored < conceided
      THEN 1 END)                                       lost,
    sum(scored) - sum(conceided)                        balance,
    sum(
        CASE WHEN scored > conceided
          THEN 3
        ELSE 0 END
        + CASE WHEN scored = conceided
          THEN 1
          ELSE 0 END)                                   points,
    count(CASE WHEN place = 'home'
      THEN 1 END)                                       home_matches,
    count(CASE WHEN place = 'home' AND scored > conceided
      THEN 1 END)                                       home_wins,
    count(CASE WHEN place = 'home' AND scored = conceided
      THEN 1 END)                                       home_draws,
    count(CASE WHEN place = 'home' AND scored < conceided
      THEN 1 END)                                       home_lost,
    SUM(CASE WHEN place = 'home'
      THEN scored
        ELSE 0 END)                                     home_scored,
    SUM(CASE WHEN place = 'home'
      THEN conceided
        ELSE 0 END)                                     home_conceided,
    count(CASE WHEN place = 'away'
      THEN 1 END)                                       away_matches,
    count(CASE WHEN place = 'away' AND scored > conceided
      THEN 1 END)                                       away_wins,
    count(CASE WHEN place = 'away' AND scored = conceided
      THEN 1 END)                                       away_draws,
    count(CASE WHEN place = 'away' AND scored < conceided
      THEN 1 END)                                       away_lost,
    SUM(CASE WHEN place = 'away'
      THEN scored
        ELSE 0 END)                                     away_scored,
    SUM(CASE WHEN place = 'away'
      THEN conceided
        ELSE 0 END)                                     away_conceided,
    GROUP_CONCAT((CASE
                  WHEN scored > conceided
                    THEN 'W'
                  WHEN scored = conceided
                    THEN 'D'
                  WHEN scored < conceided
                    THEN 'L'
                  END) ORDER BY date ASC separator '') streak
  FROM
    (
      (SELECT
         hm.date         date,
         hm.home_team_id team_id,
         hm.score_home   scored,
         hm.score_away   conceided,
         'home'          place
       FROM matches hm
       WHERE hm.season_id = :seasonId)
      UNION ALL
      (SELECT
         am.date         date,
         am.away_team_id team_id,
         am.score_away   scored,
         am.score_home   conceided,
         'away'          place
       FROM matches am
       WHERE am.season_id = :seasonId)
    ) m
    JOIN teams t ON t.id = team_id
  GROUP BY team_id
  ORDER BY points DESC, balance DESC;

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