SQL Query Driving me mad

[coleman]

hi all  hopefully someone here can help me figure this  issue out.. I had it solved once before, but i accidentally overwrote the updated code with old code.

 

i have a query that i am running that determins hockey standings.  The teams are ranked based on the game data as follows  by points, wins and plus minus.

 

i have my order by stament as follows  order by pts desc, wins desc, pm desc

 

the problem i am having is that when teams have a tie in points and wins,  the pm isn't sorting properly and for the life of me i can't figure it out.

 

here is my query

select 
 t.tsname as team,
 t.id as team_id,
 t.pf_spot,
 sum(s.gp) as GP,
 sum(s.w) as WIN,
 sum(s.l) as Loss,
 sum(s.otl) as OTL,
 ( (sum(s.w)/sum(s.gp))*100) as PCT,
 sum(s.gf) as GF,
 sum(s.ga) as GA,
  (sum(s.w)*2)+sum(s.otl) as PTS,
 (sum(s.gf) -sum(s.ga)) as PM,
 sum(s.gf)/ sum(s.gp) as GFA,
sum(s.ga) / sum(s.gp) as GAA,
sum(s.ppc) as PPC,
sum(s.ppg) as PPG,
((sum(s.ppg)/sum(s.ppc)) *100) as PPper,
sum(s.pks) as PKS,
sum(s.pka) as PKA,
    	(sum(s.pks) - sum(s.pka))/sum(s.pks)*100 as PKper,
sum(s.pim) as PIM,
sum(s.shg) as SHG
from teams t, standings s, games g
where s.team_id = t.id
and t.division ='m'
and s.season_id ='37'
and s.game_id = g.id
and g.playoff ='0'
group by team_id
order by PTS desc,W desc,PM desc 

 

the data shows up as the following:
TEAM           WINS             POINTS     PLUS MINUS
MSM                3                    6               18
MITS                3                    6               16
SJIT                  3                   6                 9
WSP                 3                    6              15

as you can see,  from the result.. the plus minus is not sorting properly.. the 4th place team should be in 3rd place.  the plus minus is a calculation and is calculated from Goals for (gf)  - Goals against (ga)

 

your help would be greatly appreciated.

 

 

[coleman]

well, after making my post, i finally found my problem.... in my order statement  W should have been WINS  as i had aliased the w field so it wasn't actually sorting by it...

 

i guess some times a good old brain fart  actually brings things to light... but if anyone has any suggestions on how i could possibly make this query better, it would be greatly appreciated. I am sure its not as efficient as it could be.

[fenway]

Should be pretty fast, since they're all constant expressions.