-- Group G of Tournament 2014 (name, tid)
WITH 
GroupG14 AS
(SELECT C.Name, T.Tid, TM.PtsWin 
  FROM Groups G, Teams T, Countries C, Tournaments TM
  WHERE G.Gid = T.Gid AND T.Cid = C.Cid AND TM.Tyear=G.TYear
  AND TM.TYear = 2014 AND G.Name='Group G'),
-- Normalized matches (home team aligned)
NormMatchG14 AS
(SELECT G.Name, M.HomeScore, M.VisitScore, G.PtsWin FROM GroupG14 G, Matches M
  WHERE M.HomeTid = G.Tid AND M.MatchType LIKE 'Group%'
UNION ALL
SELECT G.Name, M.VisitScore, M.HomeScore, G.PtsWin FROM GroupG14 G, Matches M
  WHERE M.VisitTid = G.Tid AND M.MatchType LIKE 'Group%'),
-- Indicators for win/loss
Norm2MatchG14 as 
(SELECT Name, HomeScore, VisitScore, PtsWin,
    CASE WHEN HomeScore>VisitScore THEN 1 ELSE 0 END AS Win,
    CASE WHEN HomeScore=VisitScore THEN 1 ELSE 0 END AS Draw,
    CASE WHEN HomeScore<VisitScore THEN 1 ELSE 0 END AS Loss 
  FROM NormMatchG14 M)

-- Final group table
-- (Country, matches, wins, draws, losses, goal difference, points)
SELECT Name, count(*) AS Matches, sum(Win) AS Wins, sum(Draw) AS Draws, sum(Loss) AS Losses, 
    sum(HomeScore-VisitScore) AS GoalDiff, sum(PtsWin*Win)+sum(Draw) AS Points
  FROM Norm2MatchG14 
  GROUP BY Name
  ORDER BY (sum(PtsWin*Win)+sum(Draw), sum(HomeScore-VisitScore)) DESC
