NYCPHP Meetup

[Adam Maccabee Trachtenberg]

I am trying to generate some statistics based on basketball results. I
have a table that stores the id of the winning team and the losing
team, like so:

--------------
| win | loss |
--------------
|   1 |    2 |
|   1 |    3 |
|   2 |    3 |
--------------

I want to find the total number of wins and loses for a given team. I
can do this with two queries, like so:

SELECT COUNT(*) AS w FROM games WHERE win = $team;
SELECT COUNT(*) AS l FROM games WHERE loss = $team;

Is there anyway I can do this in a single query? I'm actually less
interested in the win and loss totals than a winning percentage, so
I'd really prefer to just make the database return that number to me
instead of requiring me to calculate this in PHP.

Right now, I'm using SQLite, but I'd switch to MySQL if some
MySQL-specific function (like IF()?) could help me out and there's no
way to do this in standard SQL92.

I feel that maybe I need to use a self JOIN, but I can't get that to
pan out.

-adam

-- 
adam at trachtenberg.com
author of o'reilly's php cookbook
avoid the holiday rush, buy your copy today!