NYCPHP Meetup

[putamare]

SELECT
SUM( IF ( win = $team, 1, 0 ) )  AS w,
SUM( IF ( loss = $team, 1, 0 ) )  AS l
FROM games


On Feb 13, 2004, at 6:08 PM, Adam Maccabee Trachtenberg wrote:

> 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!
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk