Query COUNT(field) doesnt take id in consideration

Question

I have the following query:

         SELECT     dtr_user.username, 
                    dtr_user.loggedin, 
                    dtr_user.profilepicture, 
                    dtr_siterank.siterankname, 
                    dtr_user.joindate, 
                    COUNT(forum_post.postid), 
                    COUNT(dtr_entries.entreeid) 
            FROM    dtr_user, 
                    forum_post, 
                    dtr_entries, 
                    dtr_siterank 
            WHERE dtr_user.userid = ? 
            AND dtr_user.userid = dtr_entries.entreeauthor
            AND dtr_user.userid = forum_post.userid
            AND dtr_user.siterank = dtr_siterank.siterankid

So i expected it for me to return the count of postid.s of the poster, and same with count of the entree id's

When i run SELECT COUNT(forum_post.postid) FROM forum_post where forum_post.userid = 1 it returns the correct number(17), same with SELECT COUNT(dtr_entries.entreeid) FROM dtr_entries where dtr_entries.entreeauthor = 1 which returns 4

Yet in the main query it comes to a 68 on both numbers. Do I apply a left join on this? Queries have not been my string suit really.

Answer 1

I think you want:

SELECT  u.*, s.siterankname, 
    (SELECT COUNT(*) FROM forum_post fp WHERE fp.user_id = u.userid) cnt_forum_post,
    (SELECT COUNT(*) FROM dtr_entries e WHERE e.entreeauthor = u.user_id) cnt_dtr_entries
FROM dtr_user u
INNER JOIN dtr_siterank s ON s.siterankid = u.siterank
WHERE u.userid = ? 

Rationale:

• your original code is not a valid aggregation query; you need a GROUP BY clause, that should be consistent with the SELECT clause

• but even so, the results would be wrong if you have several matches per user in both the forum_post and dtr_entries tables; to avoid that, I used to correlated subqueries to count the number of matching entries in each of the two tables (which avoids the need for outer aggregation)

• I assumed that there is only one match in dtr_siterank, and used a regular join to bring the column siterankname

Notes:

• don't use implicit joins! This old syntax (with commas in the FROM clause) fell out of favor decades ago when explicit joins (with the JOIN ... ON keywords) became the standard

• table aliases make the queries easier to write and read