SQL Standings 1

[gratch]

I am relatively new to SQL and have got a standings page to work ok if I only look at the home team... here's my sample table

ScheduleTable
DIVISION HOMETEAM VISITORTEAM HOMESCORE VISITORSCORE
A leafs blues 1 1
B wings leafs 0 3
A stars leafs 0 5

I am trying to output to this
Division Team Games Played, Wins, Losses, Ties, Goals For, Goals Against, Points sorted by Points

(Points = 2 for a win and 1 for a tie)

I can't get the points to calc yet either

here is my sql statement:

SELECT division, hometeam, visitorteam, sum( homescore ) as total_goals_for, sum( visitorscore ) as total_goals_against, count( homescore ) as games_played, sum( CASE WHEN homescore > visitorscore THEN 1 ELSE 0 END ) as wins, sum( CASE WHEN homescore < visitorscore THEN 1 ELSE 0 END ) as loss, sum( CASE WHEN homescore = visitorscore THEN 1 ELSE 0 END ) as ties FROM scheduletable GROUP BY division, hometeam

I get everything to work if I only look at the home team except for the points. What I need hlp with is getting the points to calculate and include the results for the visitors as well so that I end up with table that looks like this:

Division Team GP W L T GF GA PTS
A stars 1 1 0 0 5 0 2
A blues 1 0 0 1 1 1 1
A leafs 2 0 1 1 1 6 1
B leafs 1 1 0 0 3 0 2
B wings 1 0 1 0 0 3 0

 

[bborissov]

SELECT Division,
       Team,
       COUNT(*)          AS GP,
       SUM(ScoreFor)     AS TotalGoalsFor,
       SUM(ScoreAgainst) AS TotalGoalsAgainst,
       SUM(CASE WHEN ScoreFor > ScoreAgainst
                     THEN 1
                ELSE 0 END) AS Wins,
       SUM(CASE WHEN ScoreFor < ScoreAgainst
                     THEN 1
                ELSE 0 END) AS Loss,
       SUM(CASE WHEN ScoreFor = ScoreAgainst
                     THEN 1
                ELSE 0 END) AS Ties
FROM (SELECT Division, 
             HomeTeam     AS Team ,
             HomeScore    AS ScroreFor,
             VisitorScore AS ScroreAgainst
      FROM ScheduleTable 
      UNION ALL
      SELECT Division, 
             VisitorTeam    AS Team ,
             HomeScore    AS ScroreAgainst,
             VisitorScore AS ScroreFor
      FROM ScheduleTable) Test
GROUP BY Division, Team

NOT TESTED!!!!



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gratch]

On the second line of code where you are asking for TEAM, I have no field called Team, only HomeTeam and VisitorTeam. Will it still work with a field name that isn't in the table like that?

I also have not yet tested the code but will in a little while.

 

[gratch]

I have tried the code supplied by I can't seem to get it to work

 

[haroldholt]

Try this

SELECT Division,
       Team,
       COUNT(*)          AS GP,
       SUM(ScoreFor)     AS TotalGoalsFor,
       SUM(ScoreAgainst) AS TotalGoalsAgainst,
       SUM(CASE WHEN ScoreFor > ScoreAgainst
                     THEN 1
                ELSE 0 END) AS Wins,
       SUM(CASE WHEN ScoreFor < ScoreAgainst
                     THEN 1
                ELSE 0 END) AS Loss,
       SUM(CASE WHEN ScoreFor = ScoreAgainst
                     THEN 1
                ELSE 0 END) AS Ties
FROM (SELECT Division, 
             HomeTeam     AS Team ,
             HomeScore    AS ScoreFor,
             VisitorScore AS ScoreAgainst
      FROM #ScheduleTable 
      UNION ALL
      SELECT Division, 
             VisitorTeam    AS Team ,
             VisitorScore    AS ScoreFor,
             HomeScore AS ScoreAgainst
      FROM ScheduleTable) Test
GROUP BY Division, Team

(NOT tested)

 

[gratch]

With a little poking and prodding I got it to work. Thanks to you both.

I have one question that has gone unanswered... I need to add the points into the calculation. Any suggestions?

 

[gratch]

NM... I got it all worked out... Thanks again TONS for your help. I hope I can return the favor to your forum someday.

Cheers.