CODEWITHSUNDEEP
mysqlsql
NaughtySquid
NaughtySquid

Reputation: 2097

error in sql when using group by

I have this query to group together article authors on my site, along with how many articles they've done recently and their most recent posted date.

The problem is this error:

#1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'goltest.a.author_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT
    COUNT(DISTINCT a.article_id) AS `counter`,
    u.username,
    u.user_id,
    (
    SELECT
        `date`
    FROM
        `articles`
    WHERE
        `author_id` = a.`author_id`
    ORDER BY
        `article_id`
    DESC
LIMIT 1
) AS `last_date`
FROM
    `articles` a
LEFT JOIN
    `users` u
ON
    u.user_id = a.author_id
LEFT JOIN
    `article_category_reference` c
ON
    a.`article_id` = c.`article_id`
WHERE
    a.`date` >= 1491004800 AND a.`date` <= 1495018102 AND a.`active` = 1 AND c.`category_id` NOT IN(63) AND a.author_id != 1844
GROUP BY
    u.`username`,
    u.`user_id`
ORDER BY
    `counter`
DESC
    ,
    a.date
DESC

Don't really understand why it wants "author_id" in the group by list, since I'm not selecting it?

Upvotes: 2

Views: 84

Answers (1)

NaughtySquid
NaughtySquid

Reputation: 2097

For those interested I fixed it with this, replacing the subquery with MAX() value for the date:

SELECT
    COUNT(DISTINCT a.article_id) AS `counter`,
    u.username,
    u.user_id,
    MAX(a.date) AS `last_date`
FROM
    `articles` a
LEFT JOIN
    `users` u
ON
    u.user_id = a.author_id
LEFT JOIN
    `article_category_reference` c
ON
    a.`article_id` = c.`article_id`
WHERE
    a.`date` >= 1491004800 AND a.`date` <= 1495018102 AND a.`active` = 1 AND c.`category_id` NOT IN(63) AND a.author_id != 1844
GROUP BY
    u.`username`,
    u.`user_id`
ORDER BY
    `counter`
DESC
    ,
    last_date
DESC

With thanks to Shadow for pointing out "max" :)

Upvotes: 1

Related Questions