Solved

# help with standings query

Posted on 2006-05-16
203 Views
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
Question by:gianitoo

LVL 1

Author Comment

any query guru???
0

LVL 16

Accepted Solution

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

LVL 1

Author Comment

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

#### Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!