In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'jquzntys.posts.id'

Question

I'm trying to count posts by their statuses. But below query give me this error.

Error Message: [42000][1140] In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'jquzntys.posts.id'; this is incompatible with sql_mode=only_full_group_by

SELECT COUNT(p.id)  total,
       p1.authors   authors,
       p2.published published,
       p3.pending   pending,
       p4.scheduled scheduled,
       p5.draft     draft,
       p6.deleted   deleted
FROM posts p
         LEFT JOIN (
    SELECT id, COUNT(*) as `authors`
    FROM posts
    WHERE user_id = ?
) AS p1 ON p1.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `published`
    FROM posts
    WHERE status = ?
) AS p2 ON p2.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `pending`
    FROM posts
    WHERE status = ?
) AS p3 ON p3.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `scheduled`
    FROM posts
    WHERE status = ?
) AS p4 ON p4.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `draft`
    FROM posts
    WHERE status = ?
) AS p5 ON p5.id = p.id
         LEFT JOIN (
    SELECT id, COUNT(*) as `deleted`
    FROM posts
    WHERE status = ?
) AS p6 ON p6.id = p.id

There is no such of data that need to be under group. Why I'm getting this error ?

Answer 1

If you use COUNT() but don't specify a GROUP BY clause, it still qualifies as an aggregated query, but the whole result is treated as one "group." You may think you don't have any grouping because you don't have a GROUP BY clause, but it is implicit in your use of COUNT().

It seems you want to count the number of posts of each status, and also show the total count of posts (of any status), and also show the count of posts by a specific user.

I suggest doing it this way, using two separate queries:

SELECT COUNT(*) as count
FROM posts
WHERE user_id = ?;

SELECT status, COUNT(*) as count
FROM posts
GROUP BY status WITH ROLLUP;

The advantage of the second query is that if any new status values appear in the future, you don't have to rewrite the query. The WITH ROLLUP gives you the total on the last row of the result set (on that row, status will be NULL).

Not every task needs to be implemented in a single SQL query. Sometimes splitting the query into two queries allows both queries to be simpler and more clear.