mysql counts records of multiple parentchild structures in the same query

This is because you used a lazy short cut, if you ha full_group_b<Y enabled this would immediately given an error

You have to use aggregation functions for all for all columns that are not in the GROUP BY

SELECT 
    id_forum_cat
    ,lang
    ,GROUP_CONCAT(id_forum) id_forum
    ,GROUP_CONCAT(date_insert) date_insert
    ,GROUP_CONCAT(question) question
    ,GROUP_CONCAT(description) description
    ,GROUP_CONCAT(cat) cat
    ,SUM(total_replies)
    , COUNT(*) AS total_topics
FROM (
    SELECT
    forum.id_forum_cat AS id_forum_cat,
    forum.lang AS lang,
    forum.id_forum AS id_forum,
    forum.date_insert AS date_insert,
    forum.question AS question,
    forum.description AS description,
    forum_cat.name AS cat,
    COUNT(forum_reply.id_forum_reply) AS total_replies
    FROM forum
    LEFT JOIN forum_reply ON forum_reply.id_forum = forum.id_forum
    JOIN forum_cat ON forum_cat.id_forum_cat = forum.id_forum_cat
    GROUP BY id_forum
    ORDER BY forum.date_insert DESC
) AS tmp_forum GROUP BY lang, id_forum_cat

http://sqlfiddle.com/#!9/0d11418/5

id_forum_cat    lang    id_forum    date_insert     question    description     cat     SUM(total_replies)  total_topics
1   en  3,2,1   2020-10-27 18:21:48,2020-10-27 18:13:29,2020-10-27 09:53:26     This is my third question! Right?,This is my second question. Right?,First! How do you say horse in spanish?    Im adding a third question for count purposes.,Im adding a second question for count purposes.,This is the first question of the first category. I need how to say horse in spanish in a conversation.  general,general,general     3   3
2   en  4   2020-10-27 19:17:01     First question in other category?   This is the first question of the second category.  grammar     0   1

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top