Multiple aggregate in one query

Anon

Here is my problem. I have a database which stored user votes.

The vote table looks like this:

Table vote
id PK
winner
loser
judge
......

I would like to have this kind of stats:

User had X victories and Y defeats when a certain user was the judge.

It's quite easy to find out the total of victories or defeats from a single judge, but I can't find out how to get the two aggregate results from 1 query. Is it possible?

My sql statement for finding how many victories a user had when a certain judge was involved:

select vote.winner, count(*) from vote where vote.judge = '1' GROUP BY vote.winner;

So, I get Winner name and count of victories. Now, I can I get number of defeats in the same query? Is is possible?

Your help would be really appreciated!

cheald

Try this:

count(winner) as wins, count(loser) as losses, judge from vote where judge = ? group by judge;

sarahk

can't you

select vote.winner, "w" as type, count() from vote where vote.judge = '1' GROUP BY 1
union
select vote.loser, "l" as type, count()
from vote where vote.judge = '1' group by 1

sarahk

but this only gives the stats for the judge, not the players.

Anon

I don't have a db handy to test it, but see if this is anywhere close:

select j.judge,
sum(case when w.PK is not null then 1 else 0 end),
sum(case when l.PK is not null then 1 else 0 end)
from vote j
left join vote w
on j.judge = w.judge
left join vote l
on j.judge = l.judge
group by j.judge

Anon

Yuck, I messed it up. Problem is what if the user had losses but no wins?

You might be able to do it in SQL, but I'd see if performance doing it in PHP is acceptable...

Anon

Thanks for your help!

My MySQL version is 3.x, so UNION are not accepted, but you are a great help, becaude I totally forgot about Unions! I'm not so good at SQL as you see!

Soon I will update and test your solution. At least I am on the right path!

Anon

I'm trying your solution and I see some stuff I never tried with SQL yet. Thanks a lot!

I'm not sure if my MYSQL version (3.23) supports the w.PK

I am going to take a look at this right now!

tomhath

I messed it up the first time. I misread the column name ("PK" should be "id").

The query might be made to work if you have separate tables of users and judges to join with. What else is in the db that might help?

Essentially you need a four way join: every user inner joined with every judge, left joined with the win count for that user/judge and left joined again with the lose count for the user/judge pair. You could do it all with the one table if mysql supported subselects but without that you need separate tables (as I see it anyway).