mysql - SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 -

im trying understand how mysql 5.7 works. in query bellow

      select          r.*,         concat(u.fname, ' ', u.lname) name               `events` r          left join `users` u            on r.user_id = u.id        r.event_id = 1        group r.user_id        order r.date ; 

i'm getting error " select list not in group clause , contains nonaggregated column 'r.id' not functionally dependent on columns in group clause; incompatible sql_mode=only_full_group_by [code: 42000] "

but query works fine:

  select          r.id,         r.event_id,         r.user_id,         r.date         concat(u.fname, ' ', u.lname) name               `events` r          left join `users` u            on r.user_id = u.id        r.event_id = 1        group r.user_id, r.event_id, r.id, r.date       order r.date ; 

since i'm working on localhost, can make changes my.ini file , set sql_mode "strict_all_tables,error_for_division_by_zero,no_auto_create_user"

but when going live going problem, since not many people have access my.ini file on shared hosting.

it seems me bit non-efficient declare every single field , aggregate accordingly.

since knowledge in mysql beginner, there better way write above query. in case when need pull 10 or 20 fields single table aggregating of them seems bit silly.

the concept is simple must declare in group columns not involved in aggregation function

your

select      r.id,     r.event_id,     r.user_id,     r.date     concat(u.fname, ' ', u.lname) name       `events` r      left join `users` u        on r.user_id = u.id    r.event_id = 1    group r.user_id, r.event_id, r.id, r.date   order r.date ; 

is equivalent

select distinct     r.id,     r.event_id,     r.user_id,     r.date     concat(u.fname, ' ', u.lname) name       `events` r      left join `users` u        on r.user_id = u.id    r.event_id = 1    order r.date ; 

in first have r.* not in group by