gistfile1.txt

SELECT  ue.userid AS userid,
                                   e.courseid AS courseid,
                                   f.id as forumid,
                                   SUM(CASE WHEN p.parent = 0 THEN 1 ELSE 0 END) AS postcount,
                                   SUM(CASE WHEN p.parent != 0 THEN 1 ELSE 0 END) AS replycount,
                                   u.id,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email,
                                   SUM(CASE WHEN att.attcount IS NULL THEN 0 ELSE att.attcount END) AS attachmentcount,
                                   MIN(p.created) AS earliestpost,
                                   MAX(p.created) AS latestpost, CASE WHEN tmp.viewcount IS NOT NULL THEN tmp.viewcount ELSE 0 END AS viewcount, SUM(CASE WHEN p.wordcount IS NOT NULL THEN p.wordcount ELSE 0 END) AS wordcount, SUM(CASE WHEN p.charcount IS NOT NULL THEN p.charcount ELSE 0 END) AS charcount
                             
                  FROM     {enrol} e
                                    JOIN {user_enrolments} ue ON ue.enrolid = e.id
                                    JOIN {user} u ON u.id = ue.userid
                                    JOIN {forum} f ON f.course = e.courseid
                                    JOIN {forum_discussions} d ON d.forum = f.id
                               LEFT JOIN {forum_posts} p ON p.discussion =  d.id
                                     AND p.userid = ue.userid
                                     
                               LEFT JOIN (
                                            SELECT COUNT(fi.id) AS attcount, fi.itemid AS postid, fi.userid
                                              FROM {files} fi
                                             WHERE fi.component = :component
                                               AND fi.filesize > 0
                                          GROUP BY fi.itemid, fi.userid
                                         ) att ON att.postid = p.id
                                         AND att.userid = ue.userid LEFT JOIN {forum_report_summary_counts} tmp ON tmp.userid = u.id 
                       
                 WHERE e.courseid = :courseid
                        AND f.id = :forumid
                            AND u.id = (
                                 SELECT gm.userid
                                   FROM {groups_members} gm
                                  WHERE gm.userid = u.id
                                    AND gm.groupid = :param1
                               GROUP BY gm.userid
                                  LIMIT 1)
                            AND d.groupid = :param2
                        GROUP BY ue.userid, e.courseid, f.id, u.id, u.id,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.imagealt,u.email, tmp.viewcount
                        ORDER BY firstname DESC