Solved

Away goals total

Posted on 2011-09-13
12
475 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 50 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 75 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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…

626 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