asked on

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?

gianitoo

ASKER

any query guru???

ASKER CERTIFIED SOLUTION

ThinkPaper

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gianitoo

ASKER

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