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

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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.
    68,625 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following