Jason Leveille's Blog

The Problem

A few days ago I was contacted by a colleague in need of some SQL help. He has a piece of athletic management software that tracks various metadata about teams. In his schedule table he tracks the host/opponent id and game scores, among other things. He needed an easy way to query for the number of wins, losses, and ties for each team (for all completed games). Following is the query he had constructed prior to contacting me:

SELECT id, host_id, host_score, opponent_id, opp_score,
(case
   when (host_score > opp_score) then host_id
   when (host_score < opp_score) then opponent_id
   else 0
end) as winner,
(case
   when (opp_score > host_score) then host_id
   when (opp_score < host_score) then opponent_id
   else 0
end) as loser,
(case
   when (opp_score = host_score) then opponent_id
   else 0 
end) as tie1,
(case
   when (opp_score = host_score) then host_id
   else 0
end) as tie2
FROM schedule
WHERE status = 'COM'

The query above gave him the winner and loser for each game (and whether or not the game resulted in a tie), however he needed an aggregate count of wins/losses/ties.

MySQL GROUP BY (Aggregate) Functions: SUM

I don't write SQL every day, however my instincts told me two things: 1) his solution looked much to complicated for what he was trying to accomplish (a sure sign that you might be doing something wrong), and 2) this sounded like a classic GROUP BY problem. After pulling out my trusty SQL book for reference, I constructed the following query and passed it along:

SELECT host_id, 
    SUM(host_id > opponent_id) AS wins, 
    SUM(host_id < opponent_id) AS losses, 
    SUM(host_id = opponent_id) AS ties 
FROM schedule 
WHERE status = 'COM' 
GROUP BY host_id

Conclusion

If you need aggregate data on fields in one of your database tables (AVG, COUNT, SUM, etc), your mind should immediately go to GROUP BY. It could save you time and headache.

Tagged in : MySQL