SQLite3 group by using columns, not rows

Question

What's the easiest way to fix this sqlite3 query so that wins and losses show up as columns in a single row instead of in two separate rows?

http://0890db80061d7d2b33eb4606a4c301c1.conquerclub.db.94y.info/

I can think of hard ways involving subselects, but surely there's an easy/efficient/beautiful solution here? I want to avoid something like this:

http://conquerclub.barrycarter.info/ONEOFF/aog-query.txt

Answer 1

Use:

  SELECT map, 
         SUM(CASE WHEN p.points > 0 THEN 1 ELSE 0 END) AS wins,
         SUM(CASE WHEN p.points <= 0 THEN 1 ELSE 0 END) AS lose
    FROM game g 
    JOIN players p ON g.game = p.game 
   WHERE p.player='barrycarter'
GROUP BY map