Home > IT Answers > Database > SQL join two counts on same table
Shantheman
15 pts.
 SQL join two counts on same table
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
UPDATED: September 21, 2009  11:29 PM
RELATED QUESTIONS
Answer Wiki:
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>
Last Wiki Answer Submitted:  September 21, 2009  8:02 pm  by  carlosdl   63,535 pts.
All Answer Wiki Contributors:  carlosdl   63,535 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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;
Shantheman
 15 pts.
 

Great !

Thanks for posting back.

carlosdl
 63,535 pts.