MySQL 5.7.24 COUNT() does not work correctly with sql_mode=only_full_group_by

Question

I'm trying to figure out the appropriate way of returning the posts & the total number of posts of a user in one query.

1. So the simplest way of counting the total number of posts of a user would be:

   SELECT COUNT(id) as total FROM posts WHERE uID = 37;
   
   +-------+
   | total |
   +-------+
   | 10    |
   +-------+
   

2. Next, I've changed the query to return the ID of the posts and limit the total results to the first 5 posts. But MySQL threw Error 1140...

   SELECT id, COUNT(id) as total FROM posts WHERE uID = 37 LIMIT 0,5;
   
   Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db.posts.id'; this is incompatible with sql_mode=only_full_group_by
   

3. Okay, No hard feelings... I'm not planning to change the default 'sql_mode' so I've fixed the above error by adding the GROUP BY clause to the query as suggested, but now the COUNT() function doesn't make much sense, because it returns 1 for each result... Hmmm

   SELECT id, COUNT(id) as total FROM posts WHERE uID = 37 GROUP BY id LIMIT 0,5;
   
   +----+-------+
   | id | total |
   +------------+
   |  1 | 1     |
   +----+-------+
   |  2 | 1     |
   +----+-------+
   |  3 | 1     |
   +----+-------+
   |  4 | 1     |
   +----+-------+
   |  5 | 1     |
   +----+-------+
   

4. Well, then I've added a JOIN clause for the posts so I can count the posts of the user, however I'm not very satisfied with the query, because of the additional JOIN clause and because the results has the 'total' field repeating in each row.

   SELECT id, userPosts.total as total FROM posts
       JOIN (SELECT COUNT(*) AS total FROM posts WHERE uID = 37) AS userPosts
   WHERE uID = 37 
   GROUP BY id, userPosts.total
   LIMIT 0,5;
   
   +----+-------+
   | id | total |
   +------------+
   |  1 | 10    |
   +----+-------+
   |  2 | 10    |
   +----+-------+
   |  3 | 10    |
   +----+-------+
   |  4 | 10    |
   +----+-------+
   |  5 | 10    |
   +----+-------+
   

So I'm thinking to myself.. there must be a better way of returning the posts & the total number of posts of a user in one query, so the MySQL result object would look something like this: (without the total in each post object) but I cannot find any ways.

{
  results: [
    {
      id: 1
    }, {
      id: 2
    }, {
      id: 3
    }, {
      id: 4
    }, {
      id: 5
    }
  ],
  total: 10
}

Perhaps I should create two separate MySQL queries, one for the posts of a user and another for the totals, and concatenate the two responses using a helper function on the server-side?

What do u guys suggest? Should I create two separate calls to get the results as specified above or is there any better way to improve my query?

Answer 1

You are describing a window count. In MySQL 8.0, you would do:

select id, count(*) over() as total 
from posts
where uid = 37 

If you are running an earlier version, then the [cross] join is fine. You could also express this with a correlated subquery - but the logic is the same:

select id, (select count(*) from posts p1 where p1.uid = p.uid) as total 
from posts p
where uid = 37