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.

...CASE WHEN team.teamID = awayTeamID THEN stuff for away team
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.

...SUM(
    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().

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Raymond Camden's Gravatar I'm constantly amazed by what can be done in SQL.
# Posted By Raymond Camden | 9/14/06 12:15 PM
todd's Gravatar I LOVE Case. You can pretty much put it _anywhere_ and it will work. My favorite trick is using case to conditionally join tables:

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
# Posted By todd | 9/14/06 12:49 PM
Scott Stroz's Gravatar Todd, that is cool ! Of course, now I am going to spend the rest of the day trying to find new places to use CASE.
# Posted By Scott Stroz | 9/14/06 1:29 PM
Seth Petry-Johnson's Gravatar I'm not a DBA, so don't take this as gospel, but the CASE statement might prevent the use of some indices (at best) or lead to a full table scan (at worst). That may not be a huge deal for hobby sites, but I'd pay close attention to the query's execution plan before going hog wild on a production app :)

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.
# Posted By Seth Petry-Johnson | 9/15/06 4:34 PM
snekse's Gravatar Dude, I swear you copied my database. You have the exact same structure as I do for my site. I don't think there's anything wrong with the structure. What database are you using? I'm not sure that case statement will be supported in every database - at least in the same way. I do have an if statement in one of my aggregate functions:
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
# Posted By snekse | 9/22/06 9:09 PM
Scott Stroz's Gravatar The database is MSSQL.
# Posted By Scott Stroz | 9/22/06 9:15 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9. original design by tri-star web design