SQL join two counts on same table

Shantheman

15 pts.

Tags:

MySQL query

SQL

I have two different SQL statements that each is a count on the same table, but a count of different columns. I'm trying to combine them into a single statement, but am having trouble doing so.

The two statements are:

SELECT photoIDWinner as photoID, count(primaryID) as countWin FROM `rankRaw` WHERE 1 group by `photoIDWinner`

and

 SELECT photoIDLoser as photoID, count(primaryID) as countLose FROM `rankRaw` WHERE 1 group by `photoIDLoser`

I want the result to be three columnss: photoID, countWin and countLose where photoIDWinner (from 1st) = photoIDLoser (from 2nd). Anyone able to help?

Thanks!

Software/Hardware used:
MySQL

Asked: September 21, 2009 6:17 PM Last updated: September 21, 2009 11:29 PM

Answer Wiki

Last updated: September 21, 2009 8:02 PM GMT

carlosdl84,495 pts.
History
Contributors
- Ordered by most recent
- carlosdl84,495 pts.

Thanks. We'll let you know when a new response is added.

Some example data would have been nice.

How about something like this ?

<pre>SELECT photoID, SUM(countWin), SUM(countLose)
FROM
(SELECT photoIDWinner AS photoID, COUNT(primaryID) AS countWin, 0 AS countLose
FROM rankRaw
GROUP BY photoIDWinner
UNION
SELECT photoIDLoser AS photoID, 0 AS countWin, COUNT(primaryID) AS countLose
FROM rankRaw
GROUP BY photoIDLoser)
GROUP BY photoID;</pre>

Discuss This Question: 2 Replies

Shantheman Sep 21, 2009 8:31 PM GMT
Carlosdl - sorry about not providing example data, I'll do that next time. As for your answer, it worked like a charm! Thanks so much! Yeah, creating the fake "0" columns didn't occur to me. By the way, my instance of mySQL spat out a small error just saying "Every derived table must have its own alias ", so I just added an alias at the end of the subquery that you wrote, and that did the trick. So, for anyone reading along, the final code that worked for me was:
```
SELECT photoID, SUM(countWin), SUM(countLose) 
 FROM
 (SELECT photoIDWinner AS photoID, COUNT(primaryID) AS countWin, 0 AS countLose
 FROM rankRaw
 GROUP BY photoIDWinner
 UNION
 SELECT photoIDLoser AS photoID, 0 AS countWin, COUNT(primaryID) AS countLose
 FROM rankRaw
 GROUP BY photoIDLoser) temp
 GROUP BY photoID;
```
15 pointsBadges:
report
carlosdl Sep 21, 2009 11:29 PM GMT

Great ! Thanks for posting back.

84,495 pointsBadges:

report