0

My sql looks like this: Also fiddle:

create table user(
  id varchar(45),
  name varchar(45),
  comment varchar(10)
  );
  
insert into user(id, name, comment)
values
('357607425559331', 'Name1', 'Comment'),
('240358944458028', 'Name2', 'Comment'),
('173956187882837', 'Name3', 'Comment'),
('4174662639277087', 'Name4', 'Comment'),
('1992571284232684', 'Name5', 'Comment'),
('285647222973252', 'Name6', 'Comment'),
('263639542044766', 'Name7', 'Comment'),
('483596959315457', 'Name8', 'Comment'),
('893514004824380', 'Name9', 'Comment'),
('852595485297071', 'Name10', 'Comment'),
('772471550150585', 'Name11', 'Comment');

create table insights(
  user_id varchar(45),
  date_time datetime
  );
  
insert into insights(user_id, date_time)
values
('357607425559331', '2021-04-03'),
('240358944458028', '2021-04-03'),
('173956187882837', '2021-04-03'),
('4174662639277087', '2021-04-03'),
('1992571284232684', '2021-04-03'),
('285647222973252', '2021-03-03'),
('263639542044766', '2021-04-03'),
('483596959315457', '2021-04-03'),
('893514004824380', '2021-04-03'),
('852595485297071', '2021-04-03'),
('772471550150585', '2021-04-03'),
('357607425559331', '2021-04-02'),
('240358944458028', '2021-04-02'),
('173956187882837', '2021-04-02'),
('4174662639277087', '2021-04-02'),
('1992571284232684', '2021-04-02'),
('285647222973252', '2021-04-02'),
('263639542044766', '2021-04-02'),
('483596959315457', '2021-04-02'),
('893514004824380', '2021-04-02'),
('852595485297071', '2021-04-02'),
('772471550150585', '2021-04-02'),
('357607425559331', '2021-04-01'),
('240358944458028', '2021-04-01'),
('173956187882837', '2021-04-01'),
('4174662639277087', '2021-04-01'),
('1992571284232684', '2021-04-01'),
('285647222973252', '2021-04-01'),
('263639542044766', '2021-04-01'),
('483596959315457', '2021-04-01'),
('893514004824380', '2021-04-01'),
('852595485297071', '2021-04-01'),
('772471550150585', '2021-04-01'),
('357607425559331', '2021-03-31'),
('240358944458028', '2021-03-31'),
('173956187882837', '2021-03-31'),
('4174662639277087', '2021-03-31'),
('1992571284232684', '2021-03-31'),
('285647222973252', '2021-03-31'),
('263639542044766', '2021-03-31'),
('483596959315457', '2021-03-31'),
('893514004824380', '2021-03-31'),
('852595485297071', '2021-03-31'),
('772471550150585', '2021-03-31');

And my query looks like this:

SELECT user.id as user_id, user.name as user_name 
FROM user 
LEFT JOIN insights ON user.id = insights.user_id 
WHERE (insights.date_time >= '2021-03-05') AND (insights.date_time <= '2021-04-05') 
GROUP BY user.id;

After running this query I get the following error:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Why this error appears? I mean the user_id field is unique in the user table, so it should not show this result.

On another table it works, there must be some problem with these data, but I cant figure it out.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • You should make `user.id` the primary key. – Barmar Apr 05 '21 at 19:39
  • MySQL doesn't know that it's unique unless you tell it. – Barmar Apr 05 '21 at 19:40
  • But why are yo using `GROUP BY` when you don't have any aggregate functions? Use `SELECT DISTINCT` if you just want unique results. – Barmar Apr 05 '21 at 19:41
  • The query doesn't seem to make much sense. You outer join `insights`, but then you dismiss all outer joined rows in the `WHERE` clause, rendering it a mere inner join. Then you don't select anything from `insights`, so why the join at all. As the join can multiply your `user` rows, you are forced to make them distinct and for some reason you are using `GROUP BY` for this instead of `DISTINCT`. Are you simply looking for `SELECT * FROM user WHERE id IN (SELECT user_id FROM insights WHERE date_time >= DATE '2021-03-05' AND date_time <= DATE '2021-04-05'`? – Thorsten Kettner Apr 05 '21 at 20:13
  • One more note: If `date_time` contains a time part, you probably don't want `<=` (thus including midnight of that day and not including any other time of the day), but `<` (thus excluding the whole day, i.e. midnight, too). – Thorsten Kettner Apr 05 '21 at 20:15

0 Answers0