CODEWITHSUNDEEP
phpmysql
NaughtySquid
NaughtySquid

Reputation: 2097

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?

Upvotes: 0

Views: 43

Answers (1)

ChristianF
ChristianF

Reputation: 2061

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.

Upvotes: 1

Related Questions