Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Away goals total

Posted on 2011-09-13
12
Medium Priority
?
488 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 5

Assisted Solution

by:zvytas
zvytas earned 200 total points
ID: 36530424
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 60

Accepted Solution

by:
Kevin Cross earned 300 total points
ID: 36530770
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
ID: 36531571
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Closing Comment

by:Enduro1983
ID: 36531584
Thanks zvytas and mwvisa1
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36531866
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
ID: 36531907
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
 

Author Comment

by:Enduro1983
ID: 36531943
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 60

Expert Comment

by:Kevin Cross
ID: 36531954
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 60

Expert Comment

by:Kevin Cross
ID: 36531958
Ha. I didn't see your second post. Hold on. :)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532043
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
ID: 36532127
Cheers for the help mwvisa1, i will have a look at putting it all together!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532140
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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