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)?

0


source to share


1 answer


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

      

+1


source







All Articles