Alko Alko - 18 days ago 5
MySQL Question

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

Im trying to understand how MySql 5.7 works. In query bellow

SELECT
r.*,
CONCAT(u.fname, ' ', u.lname) AS name
FROM
`events` AS r
LEFT JOIN `users` AS u
ON r.user_id = u.id
WHERE r.event_id = 1
GROUP BY r.user_id
ORDER BY r.date ;


I'm getting an error " SELECT list is not in GROUP BY clause and contains nonaggregated column 'r.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [code: 42000] "

But query like this works fine:

SELECT
r.id,
r.event_id,
r.user_id,
r.date
CONCAT(u.fname, ' ', u.lname) AS name
FROM
`events` AS r
LEFT JOIN `users` AS u
ON r.user_id = u.id
WHERE r.event_id = 1
GROUP BY r.user_id, r.event_id, r.id, r.date
ORDER BY r.date ;


Since I'm working on localhost, I can make changes to my.ini file and set sql_mode to "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

but when going live this is going to be a problem, since not to many people will have access to my.ini file especially on shared hosting.

It seems to me bit Non-efficient to declare every single field and aggregate accordingly.

Since my knowledge in MySql is just beginner, is there a better way to write above query. In a case when I need to pull 10 or 20 fields from a single table aggregating all of them seems bit silly.

Answer

The concept is is simple you must declare in group by all the columns not involved in aggregation function

your

SELECT 
    r.id,
    r.event_id,
    r.user_id,
    r.date
    CONCAT(u.fname, ' ', u.lname) AS name
  FROM
    `events` AS r 
    LEFT JOIN `users` AS u 
      ON r.user_id = u.id 
  WHERE r.event_id = 1 
  GROUP BY r.user_id, r.event_id, r.id, r.date
  ORDER BY r.date ;

is equivalent to

SELECT DISTINCT
    r.id,
    r.event_id,
    r.user_id,
    r.date
    CONCAT(u.fname, ' ', u.lname) AS name
  FROM
    `events` AS r 
    LEFT JOIN `users` AS u 
      ON r.user_id = u.id 
  WHERE r.event_id = 1 
  ORDER BY r.date ;

In the first you have r.* but this is not in group by

Comments