Just had an interesting SQL question dropped in my lap, figured I’d share the results since it was something I assumed was possible but actually ended up working exactly as I expected, for once.
A friend has a system that he uses to track what baseball uniforms were worn in each game. Each game has an ID and (among other things) the game date, the home score, the road score, the home uniform ID and the road uniform ID. From that data, he wants to be able to pull up the record of a team in a given uniform over a period of time. One particular caveat is that a jersey could be worn by either the home team or the road team (in the case of alternates that can be worn in either location).
This is how I ended up doing it:
SELECT (
(SELECT COUNT(game_id)
FROM game_log
WHERE (
(home_jersey_id = 84)
AND (home_score > road_score)
AND (
game_date BETWEEN '2010-01-01'
AND '2013-01-01'
)
)) + (SELECT COUNT(game_id)
FROM game_log
WHERE (
(road_jersey_id = 84)
AND (road_score > home_score)
AND (
game_date BETWEEN '2010-01-01'
AND '2013-01-01'
)
))
) wins,
(
(SELECT COUNT(game_id)
FROM game_log
WHERE (
(home_jersey_id = 84)
AND (home_score < road_score)
AND (
game_date BETWEEN '2010-01-01'
AND '2013-01-01'
)
)) + (SELECT COUNT(game_id)
FROM game_log
WHERE (
(road_jersey_id = 84)
AND (road_score < home_score)
AND (
game_date BETWEEN '2010-01-01'
AND '2013-01-01'
)
))
) losses
For some reason, I’d never thought about using subqueries in that way before but it just clicked to do so in this case. I kind of wonder now where else I could do that.
