Blog | Admin | Archives

SQL Case Statement

I just learned how to use the SQL ‘CASE’ statement, and my respect for Relational Databases and SQL just about tripled.

At issue was the TRC scouting program – I had implemented a system to extract all sorts of information from the data that the scouts had input, including average scoring per game. It was especially nice that we could sort based on any category. Then I added average score of the alliances per game, but I did not know how to fold that into the same SQL statement, because team colors switched between red and blue between matches. There was no single column to sum or average. So what was I to do? The solution I came up with worked well enough to win the PNW regional at least – it was to write sepearte select statements to extract the alliances’ average scores in the following manner.

First I would get the initial data dump (I know this is insecure; you can be quiet and sit down now, thank you):

$sql = "SELECT `teams`.`team_number`, `team_name`,
`carries_initial_tetra`, `teams`.`notes`,
COUNT(*) AS 'matches',
MAX(`caps`) AS 'max_caps',
MIN(`caps`) AS 'min_caps',
AVG(`caps`) AS 'avg_caps',
AVG(`contains`) AS 'avg_cont',
MAX(`auto_points`) AS 'max_auto',
MIN(`auto_points`) AS 'min_auto',
AVG(`auto_points`) AS 'avg_auto',
(AVG(`auto_points`) + AVG(`contains`) + 3 * AVG(`caps`)) AS 'avg_scoring'
FROM `teams`, `team_match`
WHERE `teams`.`team_number` = `team_match`.`team_number`
GROUP BY `team_number` ORDER BY $orderby";

Then for each alliance score I ran another SQL query (this code was more or less repeated four times):

$points = 0;
$sql = "SELECT sum(`red_score`) AS 'red_points'
FROM `matches`, `team_match`
WHERE `matches`.`match_number` = `team_match`.`match_number`
AND `alliance_color` = 'red'
AND `team_number` = '{$row['team_number']}'";
$result2 = mysql_query($sql);
if(!$result2) print mysql_errno() . ': ' . mysql_error() . "n";
else $row2 = mysql_fetch_assoc($result2);
$points += $row2['red_points'];
mysql_free_result($result2);

While this worked, it didn’t allow sorting based on the average score column, which was unfortunate but didn’t end up mattering. But the Championship (aka “Nationals”) is a whole diofferent ball game. We’ll need better analysis tools. And I had heard about this ‘CASE’ statement, and thought it might be able to help me out. So today, I learned myself how to use it. And it is truly extraordinary how much simpler it is. Check it out:


$sql = "SELECT `teams`.`team_number`, `team_name`,
`carries_initial_tetra`, `teams`.`notes`,
COUNT(*) AS 'matches',
MAX(`caps`) AS 'max_caps',
MIN(`caps`) AS 'min_caps',
AVG(`caps`) AS 'avg_caps',
AVG(`contains`) AS 'avg_cont',
MAX(`auto_points`) AS 'max_auto',
MIN(`auto_points`) AS 'min_auto',
AVG(`auto_points`) AS 'avg_auto',
(AVG(`auto_points`) + AVG(`contains`) + 3 * AVG(`caps`)) AS 'avg_scoring',
AVG(CASE WHEN `alliance_color` = 'red' THEN `red_score` ELSE `blue_score` END) AS 'avg_points',
AVG(CASE WHEN `alliance_color` = 'blue' THEN `red_score` ELSE `blue_score` END) AS 'opp_points',
AVG(CASE WHEN `alliance_color` = 'red' THEN `red_score`-`blue_score` ELSE `blue_score`-`red_score` END) AS 'diff_score'
FROM `teams`, `team_match`, `matches`
WHERE `teams`.`team_number` = `team_match`.`team_number`
AND `matches`.`match_number` = `team_match`.`match_number`
GROUP BY `team_number` ORDER BY $orderby";

As you may be able to see, the CASE statement allows me to do a conditional sum. And now I can sort, and yes, the TRC does appear at the top, yes, yes indeed.

Leave a Reply