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?
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;
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 2  Replies