Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

Away goals total

Hi,

I have got the query at the bottom working to show a league table and also the home goals total, but I want to display away goals total for each team also, so when I change

SELECT TeamTitle, SUM(GoalsH) As THGoals, SUM(Points) AS TotalPoints

to

SELECT TeamTitle, SUM(GoalsH) As THGoals, SUM(GoalsA) As TAGoals,, SUM(Points) AS TotalPoints

I get an error  ---  Invalid column name 'GoalsA'.   I am sure it is something to do with the union all line because the declaration AwayGoals AS GoalsA lline is after that, any help would be great,  i also want to do a line to show homegoals minus away goals, something like THGoals - TAGoals As OverallGoals

DECLARE @seasonYear INT;
DECLARE @calendarVal DATETIME;
SET @seasonYear = 1993;
SET @calendarVal = 14/12/1993;

SELECT TeamTitle, SUM(GoalsH) As THGoals, SUM(Points) AS TotalPoints
FROM (

-- first get all the home team statistics
SELECT MatchDate
     , HomeTeam AS TeamTitle
     , HomeGoals AS GoalsH
     , Result
     , CASE
          WHEN HomeGoals > AwayGoals THEN 3
          WHEN HomeGoals = AwayGoals THEN 1
          ELSE 0
       END AS Points
            
FROM [results]
WHERE [Season] = @seasonYear

UNION ALL

-- second get all the away team statistics
SELECT MatchDate
     , AwayTeam AS TeamTitle
     , AwayGoals AS GoalsA
     , Result
     , CASE
          WHEN AwayGoals > HomeGoals THEN 3
          WHEN AwayGoals = HomeGoals THEN 1
          ELSE 0
       END AS Points
FROM [results]
WHERE [Season] = @seasonYear

) derived
GROUP BY TeamTitle
ORDER BY TotalPoints DESC
;
0
Enduro1983
Asked:
Enduro1983
  • 6
  • 5
2 Solutions
 
zvytasCommented:
Use the following SQL:

DECLARE @seasonYear INT;
DECLARE @calendarVal DATETIME;
SET @seasonYear = 1993;
SET @calendarVal = 14/12/1993;

SELECT TeamTitle, SUM(GoalsH) As THGoals, SUM(Points) AS TotalPoints
FROM (

-- first get all the home team statistics
SELECT MatchDate
     , HomeTeam AS TeamTitle
     , 0 AS GoalsA
     , HomeGoals AS GoalsH
     , Result
     , CASE
          WHEN HomeGoals > AwayGoals THEN 3
          WHEN HomeGoals = AwayGoals THEN 1
          ELSE 0
       END AS Points
           
FROM [results]
WHERE [Season] = @seasonYear

UNION ALL

-- second get all the away team statistics
SELECT MatchDate
     , AwayTeam AS TeamTitle
     , AwayGoals AS GoalsA
     , 0 AS GoalsH
     , Result
     , CASE
          WHEN AwayGoals > HomeGoals THEN 3
          WHEN AwayGoals = HomeGoals THEN 1
          ELSE 0
       END AS Points
FROM [results]
WHERE [Season] = @seasonYear

) derived
GROUP BY TeamTitle
ORDER BY TotalPoints DESC
;
0
 
Kevin CrossChief Technology OfficerCommented:
zvytas, I have not looked hard at your query, so apologies if I missed you are suggesting this same thing.
Enduro1983, since I helped in the building of the query, I figured I would make a comment. It sounds like you want to do what we were talking about and show SUM() of goals against, like a Points For vs Points Against stat in most leagues. See below (issue is you have to add the columns you want in the inner query first to make it available to the outer one).
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
;

Open in new window


Now in typing that code, I had the thought that you may be looking to get the team's goals when they are home team separate from when they are away team. If that is the case, then something like this, using conditional aggregates:
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
;

Open in new window

0
 
Enduro1983Author Commented:
Thanks mwvisa1,!  I can see how you can use SQL in more traditional programming constructs but I still can't get the overall total goals? I am not sure how to sum the difference between homegoals and awaygoals, i was trying something like SUM(THGoals - TAGoals) AS TotalGoals??
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Enduro1983Author Commented:
Thanks zvytas and mwvisa1
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, so you are trying to get point differential type information. It is still unclear as to what those represent to you. Can you show with actual data. i.e., is away goals the goals of the other team? Remember because you are getting one team's totals, you have all their goals in ONE column whether that team was home team one game or away team the other. So the concept of home versus away goals are a bit different. Hence my use of goals for and goals against to clarify when I mean the other team whereas goals home and goals away would be the conditional aggregate filtering the current team's goal based on HomeOrAway column added to the UNION.

So note total goals is simply SUM(GoalsFor). Now as a point differential if we are using "away" to mean other team would be the first query in my post above like this SUM(GoalsFor - GoalsAgainst).

Does that make more sense?
0
 
Enduro1983Author Commented:
Hi mwvisa1,

it is just a simple subtraction as you stated from what the team scored minus what they coinceded


Man United      39      41      92
Blackburn      31      32      84
Newcastle      51      31      77
Arsenal      25      28      71
Leeds      37      28      70
Wimbledon      35      21      65
0
 
Enduro1983Author Commented:
sorry i hit enter by mistake..

I think what i need is a bit of both, i need to know how many goals a team scored at home and away and also how many a team conceded home and away, i think between the examples all the code is there, but not I am not sure how to perform a calculation between the two values,

this is a sample record, home team, away team, hgoals, agoals, result, season, date of match

Man United      Aston Villa      3      1      H      2006      13/01/2007 00:00:00

i am after something like this eventually

http://www.statto.com/football/stats/england/premier-league/1993-1994


0
 
Kevin CrossChief Technology OfficerCommented:
Cool, then yes, it is the first query:
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
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Ha. I didn't see your second post. Hold on. :)
0
 
Kevin CrossChief Technology OfficerCommented:
I included an AVG() differential that uses ABS() -- absolute value. This hopefully gives you an example of getting something like average point swing good or bad over season, i.e., games always within 1 goal. If you want to get really fancy: http://msdn.microsoft.com/en-us/library/ms177516.aspx
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
;

Open in new window

0
 
Enduro1983Author Commented:
Cheers for the help mwvisa1, i will have a look at putting it all together!
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome. For wins/loses, you can count when Result = HomeOrAway since it appears they are both either 'H' or 'A' and if result is neither is a draw. Then you can count only those wins that are 'H' and so forth. Good luck.

Cheers,
Kevin
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now