SQL join two counts on same table

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: Last updated:
Related Questions
1

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Shantheman
    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
    Great ! Thanks for posting back.
    86,030 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: