Liam Dawe Liam Dawe - 2 months ago 8
MySQL Question

can't use mysql count in this query

I have this query right now to grab comments on my website:

SELECT a.author_id,
a.guest_username,
a.comment_text,
a.comment_id,
u.pc_info_public,
u.distro,
a.time_posted,
a.last_edited,
a.last_edited_time,
u.username,
u.user_group,
u.secondary_user_group,
u.`avatar`,
u.`avatar_gravatar`,
u.`gravatar_email`,
u.`avatar_uploaded`,
u.`avatar_gallery`,
u.pc_info_filled,
ul.username as username_edited
FROM `articles_comments` a
LEFT JOIN `users` u ON a.author_id = u.user_id
LEFT JOIN `users` ul ON ul.user_id = a.last_edited
WHERE a.`article_id` = ? ORDER BY a.`comment_id` ASC LIMIT ?


I'm trying to add-in a COUNT(l.likes) from a table called "likes" (the "l" being what I name it when doing the ON check).

The problem is, when I add the COUNT in, my query instantly fails with an error like this:


Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'goltest.a.author_id'; this is incompatible with sql_mode=only_full_group_by


I've googled and googled and all the examples I can find to fix it don't make sense to me, can someone elaborate on the error?

Answer

The error message tells you what's wrong: You need to group by something, to count how many likes that something has. The grouping is usually done on the primary key on which you're joining the tables.