Liam Dawe Liam Dawe - 1 year ago 54
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download