• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

help with standings query

i have a project and need some help on creating this complicated query which i think is possible

lets say that i have 3 teams and each team plays 3 games against each other.  

hometeam   |    visitteam   |  gamenumber  |    scorehome   |   scorevisit   |

carmel               cosmos             100                      1                     0
cosmos               challe               101                      2                     0
challe                   carmel            102                     0                     0



so in this scenario    carmel will have       1 game won    ,    1 tie   = 4 points   (3 points if you win  and 1 if you tie)
cosmos     1 games won  =  3 points
challe = 1 tie = 1 point  



so standings  look like this

teamname   |   games played   |   goalsinfavor   |   goalsagainst   |  Won   | loss   | tie  |  points

carmel                     2                        1                           0                1         0         1         4
cosmos                    2                         2                          1                 1         1          0        3
challe                       2                         0                           2                0         0          1        1



is it possible  to create this query  and diaplay recordset results like the ones above?
0
gianitoo
Asked:
gianitoo
  • 2
1 Solution
 
gianitooAuthor Commented:
any query guru???
0
 
ThinkPaperCommented:
I'm wondering if it's possibly easier for you to just do a standard SELECT query and then using ASP, do the calculations and display the data how you want it...



0
 
gianitooAuthor Commented:
i figured it out  

SELECT     t .TeamName, COUNT(*) [Games Played], SUM(CASE WHEN
> statecupnewschedule.home = t .TeamName THEN homescore ELSE awayscore
> END)
>                       GoalsInFavor, SUM(CASE WHEN
> statecupnewschedule.home = t .TeamName THEN awayscore ELSE homescore
> END) GoalsAgainst,
>                       SUM(CASE WHEN statecupnewschedule.home = t
> .TeamName AND homescore > awayscore THEN 1 ELSE 0 END)
>                       + SUM(CASE WHEN statecupnewschedule.away = t
> .TeamName AND awayscore > homescore THEN 1 ELSE 0 END) Won,
>                       SUM(CASE WHEN statecupnewschedule.home = t
> .TeamName AND homescore < awayscore THEN 1 ELSE 0 END)
>                       + SUM(CASE WHEN statecupnewschedule.away = t
> .TeamName AND awayscore < homescore THEN 1 ELSE 0 END) Lost,
>                       SUM(CASE WHEN (statecupnewschedule.home = t
> .TeamName OR
>                       statecupnewschedule.away = t .TeamName) AND
> awayscore = homescore THEN 1 ELSE 0 END) Tied,
>                       (SUM(CASE WHEN statecupnewschedule.home = t
> .TeamName AND homescore > awayscore THEN 1 ELSE 0 END)
>                       + SUM(CASE WHEN statecupnewschedule.away = t
> .TeamName AND awayscore > homescore THEN 1 ELSE 0 END))
>                       * 3 + SUM(CASE WHEN (statecupnewschedule.home =
> t .TeamName OR
>                       statecupnewschedule.away = t .TeamName) AND
> awayscore = homescore THEN 1 ELSE 0 END) Points
> FROM         statecupnewschedule JOIN
>                           (SELECT DISTINCT home TeamName
>                             FROM          statecupnewschedule
>                             UNION
>                             SELECT DISTINCT away
>                             FROM         statecupnewschedule) t ON
> statecupnewschedule.home = t .TeamName OR statecupnewschedule.away = t
> .TeamName GROUP BY t .TeamName ORDER BY Points DESC
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now