Combining queries in MySQL
I know that you can combine multiple table items using the Join operator, but is there a way to combine these two queries into one ?
SELECT Statistics.StatisticID
FROM Statistics
ORDER BY `SubmittedTime` DESC
LIMIT 0, 10
And this one?
SELECT COUNT(Votes.StatisticID)
FROM Votes
WHERE Votes.StatisticID = ?
(fluff removed)
At the moment I have achieved something that almost works.
SELECT Statistics.StatisticID, COUNT(Score.StatisticID) AS Votes
FROM `Statistics`
LEFT JOIN `Votes` AS `Score` ON `Statistics`.`StatisticID` = `Score`.`StatisticID`
ORDER BY `SubmittedTime` DESC
LIMIT 0, 10
The table Votes
is a simple static identifier, the UserID collector. In my test case it contains 3 rows, two with StatID 5 - 1 with StatID 2.
My query will work if I add a WHERE clause like WHERE StatisticID = 5
and return 2 votes correctly. However, if I remove the WHERE clause, I still get one row and 3 votes.
Is it possible to combine these queries or do I have to run a second one for each result (obviously not preferable)?
source to share
Assuming you want to count the number of votes for statistics
SELECT StatisticID, COUNT(*) AS CountVotes
FROM `Votes`
GROUP BY Statistics.StatisticsID
I see no reason why the tables should be merged.
[EDIT] Ah ... I see that you want to order at the presented time in the statistics table.
SELECT Votes.StatisticID, COUNT(*) AS CountVotes
FROM `Votes` JOIN Statistics ON votes.statisticsID = Statistics.StatisticsID
GROUP BY Statistics.StatisticsID
ORDER BY Statistics.SubmittedTime
source to share