need help on a soccer query

Posted on 2004-11-10
Last Modified: 2012-06-27
I created a program where clubs can see standings for their teams.   with this query below, i can generate standings.    this will display the one who got more points on top followed by goals in favor.    

In this league there are 2 rules that i need to apply to this query.   if points are the same , then it should go by head to head competition , if both are the same (points and head to head) then it isdetermined by goal differential.  

I will assume that goal diferential can be created by ( SUM(fallgameresults.goalsinfavor) - SUM(fallgameresults.goalsagainst) )     how can i set this up in this query?     an also how can i figure out the head to  head competition in this query (this means that if team "A" played against team "B" and team "A" won the game then if it happens that they have same points in total then team "A" will be in first place)

SELECT fallgameresults.idofteam, SUM(fallgameresults.goalsinfavor)   AS goals, SUM(fallgameresults.goalsagainst) AS against,   SUM(fallgameresults.points) AS points,                           SUM( AS win, SUM(fallgameresults.tie)   AS tie, SUM(fallgameresults.loss) AS loss, SUM(fallgameresults.gameplayed) AS played, ,                        databaseteams.TeamName,databaseteams.div
FROM fallgameresults INNER JOIN                          databaseteams ON fallgameresults.idofteam =    GROUP BY fallgameresults.idofteam,  , databaseteams.TeamName,databaseteams.div  having databaseteams.div ='varage'
ORDER BY points desc,goals desc
Question by:gianitoo
    LVL 50

    Expert Comment

    which columns on fallgameresults  specify the home and away teams?

    LVL 1

    Author Comment

         [gamesid] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
          [juego] [int] NULL ,      this   is= to game number
          [idofteam] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,   this is the teamid  
          [goalsinfavor] [int] NULL ,
          [goalsagainst] [int] NULL ,
          [points] [int] NULL ,
          [win] [int] NULL ,
          [tie] [int] NULL ,
          [loss] [int] NULL ,
          [gameplayed] [int] NULL ,
          [submitted] [datetime] NULL
    LVL 1

    Author Comment

    In my schedule database   i   have them set up as home and away.    

          [Div] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [GameDate] [smalldatetime] NULL ,
          [Gametime] [smalldatetime] NULL ,
          [Site] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [HomeID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [HomeScore] [int] NULL ,
          [VisitID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [VisitScore] [int] NULL ,
          [Site1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [status] [int] NULL ,
          [statushome] [int] NULL ,
          [statusvisitor] [int] NULL ,
          [noshow] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
          [updateddate] [datetime] NULL
    LVL 50

    Expert Comment

    we need to know how many times the teams have played and which won...

    can schedule be related to gamesresult?

    otherwise this can't be determined...

    i think you only need 1 table out of schedule/gamesresult (probably schedule)

    unless you normalise both and get rid of the redundancies..

    how do you see the tables relating / your application working?
    LVL 1

    Author Comment

    could you give an idea on how would you set up the table?
    LVL 1

    Author Comment

    also how do i at least for now do the goal differential in my query?
    LVL 50

    Accepted Solution

    Select idofteam,goals,against,goals - against as diff,points
             , win,tie,loss,played,id,teamname
    --  into #temp
      from (
             SELECT g.idofteam, SUM(g.goalsinfavor)   AS goals, SUM(g.goalsagainst) AS against
                  , SUM(g.points) AS points
                  , SUM( AS win
                  , SUM(g.tie)   AS tie
                  , SUM(g.loss) AS loss
                  , SUM(g.gameplayed) AS played
                 ,  t.TeamName
               FROM fallgameresults as g
              INNER JOIN databaseteams as t
                 ON g.idofteam =  
              Where t.div ='varage'
              GROUP BY g.idofteam,,t.teamname
           ) as x  
    ORDER BY points desc,4 desc


    Expert Comment

    hi gianitoo
                    to me the table design seems to   unnormalized.
    there are two entities, one team  , two game. now one team can play more than one game
    and one game includes more than one team , so the relation ship between them is many to many, inthis case a third table which will be a sandwitch table between these two tables which u have shown.

    table TEAM

    table GAME


    table TEAM_GAME


    i dont have much time right now, try to change it then apply query,
     let me nknow if i m sure about ur question


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    761 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

    7 Experts available now in Live!

    Get 1:1 Help Now