SELECT list is not in GROUP BY clause and contains nonaggregated column; this is incompatible with sql_mode=only_full_group_by

Question

This is my query using Hibernate:

Query query = session.createQuery("
              From Invite 
             where (mobileNo= :email OR mobileNo= :phone) AND status= :status GROUP BY job");

and I am getting error:

        SELECT list is not in 
      GROUP BY clause and contains nonaggregated column 'irecruter.invitecand0_.invite_id' which is not functionally dependent on columns in GROUP BY clause; this is  incompatible with sql_mode=only_full_group_by

And I got solution from StackOverflow : SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But the error back again. I want permanent solution. can anybody give suggession on this?

Answer 1

Imagine a table where forum-posts are stored. You store the post itself, the date of the post and which user posted it.

When you do something like:

SELECT
    User_ID,
    PostedDate,
    COUNT(*) AS Posts
FROM myForum
WHERE PostedDate = CURDATE()
GROUP BY User_ID;

We all know what you want. A database would say "User_ID? yeah. It must be unique after the group by. Posts are aggregated by the group by but what the hell should I do with PostedDate? which one should I return?!?". In this case, it would be unique but if the where-clause would not check for an exact "PostedDate" but a range for example, the statement doesn't define which value should be returned.

This setting in the safe-mode protects someone from making a logical error like this. If you are aware what you do, you can disable the check or do something like "MIN", "GROUP_CONCAT(DISTINCT PostedDate) AS PostedDate",...

In a case like this you can also use ANY_VALUE(), which picks one value within that group. Use it like:

SELECT
    ANY_VALUE(myUsers.UserName) AS UserName,
    User_ID,
    COUNT(ForumPosts.Post) AS Posts
FROM myUsers
NATURAL JOIN ForumPosts
WHERE ForumPosts.PostDate = CURDATE()
GROUP BY User_ID;