DECLARE @seasonYear INT;
DECLARE @calendarVal DATETIME;
SET @seasonYear = 1993;
SET @calendarVal = 14/12/1993;
SELECT TeamTitle
, SUM(GoalsFor) As GoalsFor
, SUM(Points) AS TotalPoints
, SUM(GoalsAgainst) AS GoalsAgainst
FROM (
-- remember columns names in UNION go by first query
-- first get all the home team statistics
SELECT MatchDate
, HomeTeam AS TeamTitle
, HomeGoals AS GoalsFor
, Result
, CASE
WHEN HomeGoals > AwayGoals THEN 3
WHEN HomeGoals = AwayGoals THEN 1
ELSE 0
END AS Points
, AwayGoals AS GoalsAgainst
FROM [results]
WHERE [Season] = @seasonYear
UNION ALL
-- second get all the away team statistics
SELECT MatchDate
, AwayTeam AS TeamTitle
, AwayGoals AS GoalsFor
, Result
, CASE
WHEN AwayGoals > HomeGoals THEN 3
WHEN AwayGoals = HomeGoals THEN 1
ELSE 0
END AS Points
, HomeGoals AS GoalsAgainst
FROM [results]
WHERE [Season] = @seasonYear
) derived
GROUP BY TeamTitle
ORDER BY TotalPoints DESC
;
DECLARE @seasonYear INT;
DECLARE @calendarVal DATETIME;
SET @seasonYear = 1993;
SET @calendarVal = 14/12/1993;
SELECT TeamTitle
, SUM(CASE HomeOrAway WHEN 'H' THEN GoalsFor ELSE 0 END) AS THGoals
, SUM(CASE HomeOrAway WHEN 'A' THEN GoalsFor ELSE 0 END) AS TAGoals
, SUM(Points) AS TotalPoints
FROM (
-- first get all the home team statistics
SELECT MatchDate
, HomeTeam AS TeamTitle
, HomeGoals AS GoalsFor
, Result
, CASE
WHEN HomeGoals > AwayGoals THEN 3
WHEN HomeGoals = AwayGoals THEN 1
ELSE 0
END AS Points
, 'H' AS HomeOrAway
FROM [results]
WHERE [Season] = @seasonYear
UNION ALL
-- second get all the away team statistics
SELECT MatchDate
, AwayTeam AS TeamTitle
, AwayGoals AS GoalsFor
, Result
, CASE
WHEN AwayGoals > HomeGoals THEN 3
WHEN AwayGoals = HomeGoals THEN 1
ELSE 0
END AS Points
, 'A' AS HomeOrAway
FROM [results]
WHERE [Season] = @seasonYear
) derived
GROUP BY TeamTitle
ORDER BY TotalPoints DESC
;
DECLARE @seasonYear INT;
DECLARE @calendarVal DATETIME;
SET @seasonYear = 1993;
SET @calendarVal = 14/12/1993;
SELECT TeamTitle
, SUM(GoalsFor) As GoalsFor
, SUM(GoalsAgainst) AS GoalsAgainst
, SUM(GoalsFor - GoalsAgainst) AS TotalGoals
, SUM(Points) AS TotalPoints
FROM (
-- remember columns names in UNION go by first query
-- first get all the home team statistics
SELECT MatchDate
, HomeTeam AS TeamTitle
, HomeGoals AS GoalsFor
, Result
, CASE
WHEN HomeGoals > AwayGoals THEN 3
WHEN HomeGoals = AwayGoals THEN 1
ELSE 0
END AS Points
, AwayGoals AS GoalsAgainst
FROM [results]
WHERE [Season] = @seasonYear
UNION ALL
-- second get all the away team statistics
SELECT MatchDate
, AwayTeam AS TeamTitle
, AwayGoals AS GoalsFor
, Result
, CASE
WHEN AwayGoals > HomeGoals THEN 3
WHEN AwayGoals = HomeGoals THEN 1
ELSE 0
END AS Points
, HomeGoals AS GoalsAgainst
FROM [results]
WHERE [Season] = @seasonYear
) derived
GROUP BY TeamTitle
ORDER BY TotalPoints DESC
;
DECLARE @seasonYear INT;
DECLARE @calendarVal DATETIME;
SET @seasonYear = 1993;
SET @calendarVal = 14/12/1993;
SELECT TeamTitle
-- calculate team goals
, SUM(GoalsFor) AS TotalGoals
, SUM(CASE HomeOrAway WHEN 'H' THEN GoalsFor ELSE 0 END) AS THGoals
, SUM(CASE HomeOrAway WHEN 'A' THEN GoalsFor ELSE 0 END) AS TAGoals
-- calculate opponent goals
, SUM(GoalsAgainst) AS TotalOpponentGoals
, SUM(CASE HomeOrAway WHEN 'H' THEN GoalsAgainst ELSE 0 END) AS OHGoals
, SUM(CASE HomeOrAway WHEN 'A' THEN GoalsAgainst ELSE 0 END) AS OAGoals
-- additional statistics
, SUM(GoalsFor - GoalsAgainst) AS TotalGoalDiff
, AVG(ABS(GoalsFor - GoalsAgainst)) AS AvgGoalDiff
, SUM(CASE HomeOrAway WHEN 'H' THEN GoalsFor - GoalsAgainst ELSE 0 END) AS THGoalDiff
, SUM(CASE HomeOrAway WHEN 'A' THEN GoalsFor - GoalsAgainst ELSE 0 END) AS TAGoalDiff
, SUM(Points) AS TotalPoints
FROM (
-- first get all the home team statistics
SELECT MatchDate
, HomeTeam AS TeamTitle
, HomeGoals AS GoalsFor
, Result
, CASE
WHEN HomeGoals > AwayGoals THEN 3
WHEN HomeGoals = AwayGoals THEN 1
ELSE 0
END AS Points
, 'H' AS HomeOrAway
, AwayGoals AS GoalsAgainst
FROM [results]
WHERE [Season] = @seasonYear
UNION ALL
-- second get all the away team statistics
SELECT MatchDate
, AwayTeam AS TeamTitle
, AwayGoals AS GoalsFor
, Result
, CASE
WHEN AwayGoals > HomeGoals THEN 3
WHEN AwayGoals = HomeGoals THEN 1
ELSE 0
END AS Points
, 'A' AS HomeOrAway
, HomeGoals AS GoalsAgainst
FROM [results]
WHERE [Season] = @seasonYear
) derived
GROUP BY TeamTitle
ORDER BY TotalPoints DESC
;
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.