CODEWITHSUNDEEP
mysqlgroup-bysql-mode
sofquestion 9
sofquestion 9

Reputation: 91

Not able to use GROUP BY with join query in MySQL

I need to execute the below Query but not able to do it.

SELECT * 
FROM (SELECT * 
      FROM rider_status 
      order by created_date DESC) as riderActice 
INNER JOIN rider_user ON rider_userId = rider_user.id 
Where rider_user.isActive = 1 
AND online_status = 1 
GROUP by rider_userId

Error: "#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'riderActice.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

I have read some blogs and found the below solution.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But Yet executing above solution I am getting another issue which is 

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Please let me know if I am missing something or doing it wrong

Upvotes: 2

Views: 1498

Answers (1)

VBoka
VBoka

Reputation: 9083

You can not select all(* means all) columns from a table and do a group by one column. That is why you are getting an error. If the column is in the select clause and it is not a part of aggregate function then it has to be in a group by clause.

Also, use the aliases you have created riderActice when joining two tables(subquery and a table).

Here is a small demo demonstrating first part of my answer. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b10244e667e59bffb146170014dc69ba

If you want to select all columns then do it like this:

SELECT riderActice.rider_userId
         , riderActice.created_date
         , rider_user.id
         , rider_user.isActive
         , rider_user.online_status 
FROM rider_status as riderActice 
INNER JOIN rider_user ON riderActice.rider_userId = rider_user.id 
WHERE rider_user.isActive = 1 
AND rider_user.online_status = 1 
GROUP BY riderActice.rider_userId
         , riderActice.created_date
         , rider_user.id
         , rider_user.isActive
         , rider_user.online_status

If you do not want to group by every column then explain to us what is it that you need, but this is the answer to your question.

Upvotes: 1

Related Questions