The `CASE` statement allows for some basic logic in SQL.
```SQL
SElECT columnA, columnB,
CASE
WHEN <condition1> THEN <result1>
WHEN <condition2> THEN <result2>
ELSE <defaultResult> -- optional
END <returnColumn>
FROM <table>;
```
As always, use single quotes for strings in the result. Make sure a comma separates the other columns in the query.
A common use of `CASE` is counting instances of specific conditions. Combine with `SUM()`. For example, imagine a table of soccer games and a table of goals scored. You want the final score of each match.
```sql
SELECT
mdate,
team1,
SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
team2,
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
FROM game JOIN goal ON matchid = id
GROUP BY mdate, matchid, team1, team2;
```
Returns
|mdate|team1|score1|team2|score2|
|---|---|---|---|---|
|1 July 2012|ESP|4|ITA|0|
|10 June 2012|ESP|1|ITA|1|
|10 June 2012|IRL|1|CRO|3|