MySQL: group by `topic_id`, if `topic_id` > 0
I have entries
which might or might not be assigned to a topic
.
I want to select entries with the highest score by summing up the score of those, which belong to the same topic. In addition, I want to get a number of similar entries, which were grouped together.
SELECT *, COUNT(`topic_id`) FROM `entries`
GROUP BY `topic_id` HAVING `topic开发者_开发知识库_id` > 0
ORDER BY SUM(`score`) DESC LIMIT 30
This query misses a few things. Firstly, I want entries without topic_id (topic_id = 0) not to be grouped, but to be treated individually. Secondly, COUNT(topic_id
) does not always return a real number of entries belonging to the same topic.
SELECT * FROM
(
SELECT topic_id,COUNT(*),SUM(`score`) AS sum
FROM `entries`
WHERE `topic_id` > 0
GROUP BY `topic_id`
UNION
SELECT topic_id, 1,1 AS sum
FROM `entries`
WHERE `topic_id` = 0
)
ORDER BY sum DESC LIMIT 30
SELECT
`topic_id` > 0 IsGroupedTopid,
if(`topic_id`>0, `topic_id`, id) TopicOrEntryId,
COUNT(*) CountEntries,
SUM(`score`) TotalScore
FROM `entries`
GROUP BY
`topic_id` > 0,
if(`topic_id`>0, `topic_id`, id)
ORDER BY SUM(`score`) DESC
LIMIT 30;
精彩评论