SUM() cool stuff
One of my personal projects is an on-line football pool that is run by a fried of mine. The pool is fairly simple, you pick 2 teams each week who you think will have the highest margin of victory (MOV), the person who has the highest aggregate MOV at the end of the season wins.
I was working on a 'stats' page yesterday to show information about the teams chosen by the entrants, as well as MOV information based on team, conference, division, etc... and ran into a potential issue with my database structure.
I have a 'game' table, which has the following columns: gameID, SeasonID, week, homeTeamID, awayTeamID, homeScore, awayScore. The potential issue was that for any given week, a team could be in the awayTeamID column, or the homeTeamID column. It was easy enough to determine this by using a CASE...WHEN statement in my SQL.
ELSE some stuff for home team
There was one problem with this, one of the 'reports' I was working on required the season-long MOV for every team, which means I needed to use SUM(). I know I could have handled this in CF, but I prefer to let the database do what it is good at, and try to avoid unnecessary processing of data with CF.
On a whim, I decided to try something rather odd, and surprisingly, it worked. I tried putting the case statement inside the SUM() call, like this.
CASE WHEN t.teamid = gh.homeTeamID THEN gh.homeScore-gh.awayScore
ELSE gh.awayScore-gh.homeScore
END
) AS MOV
...
Basically what this does is use the CASE statement to determine if the team is the home team, if so, it calculates the MOV as homeScore-awayScore. If the team is not the home team, they must be the away team, so the MOV is calculated as awayScore-homeScore.
I think its pretty cool that you can put logic inside of the SUM() call. I have not tested it, but I would imagine you could also add logic inside other aggregate functions like MAX() or MIN().
select stuff
from aTable as a
left outer join bTable as b
on case when a.something = 'this' then a.something else a.somethingElse = b.something
I do love using CASE when I can get away with it though... in some cases the SQL statement is much, much easier to manage than the alternative.
Count(IIf(pickscore > 0, 1, Null)) as WCount
The way I tended to get around the same issue was to include the table twice under different aliases. Not sure if it's any better, but it's easier for me to read and wrap my head around.
SELECT g.hometeamscore, g.awayteamscore,h.hometeamname, a.awayteamname
from game g,
(Select teamid as hometeamid, teamcode as hometeamcode, teamname as hometeamname from team ) h,
(Select teamid as awayteamid, teamcode as awayteamcode, teamname as awayteamname from team ) a
where 1=1
AND g.hometeamid = h.hometeamid
AND g.awayteamid = a.awayteamid