Solved

Away goals total

Posted on 2011-09-13
12
456 Views
Last Modified: 2012-05-12
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
Comment
Question by:Enduro1983
  • 6
  • 5
12 Comments
 
LVL 5

Assisted Solution

by:zvytas
zvytas earned 50 total points
Comment Utility
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 75 total points
Comment Utility
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
 

Author Comment

by:Enduro1983
Comment Utility
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
 

Author Closing Comment

by:Enduro1983
Comment Utility
Thanks zvytas and mwvisa1
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:Enduro1983
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Enduro1983
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Ha. I didn't see your second post. Hold on. :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:Enduro1983
Comment Utility
Cheers for the help mwvisa1, i will have a look at putting it all together!
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now