Tell me where is my error? Switched the muscle version from 5.6 to 5.7 and an error appeared

SELECT id, COUNT(*) FROM poi Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'shelter.poi.id'; this is incompatible with sql_mode=only_full_group_by

I make a grouping and it ceases to count the number correctly.

 SELECT id, COUNT(*) FROM poi GROUP BY id

enter image description here

  • And why do you print the ID column, what value do you want to get in it when you count the number of rows? without only_full_group_by there would be the first value that does not carry any meaning - Mike
  • If you disable only_full_group_by and remove the GROUP BY id, then it displays as it should be the number of lines (equals 7). I would like to know what I am doing in the request wrong about this error, because only_full_group_by in version 5.7 is enabled by default and stupid to do SET sql_mode = ''; not a good option for me in this case ... - RSalnikov
  • one
    This count () returns the number of rows. but you say select id, count(*) I ask why there is an id column? If you remove it, the request will of course be fulfilled - Mike
  • Everything, I already understood what was the error =) - RSalnikov

1 answer 1

I don’t know if it’s right or not, but this query works

 SELECT id, (SELECT COUNT(*) FROM poi) FROM poi
  • did you really need all the records and the total number next to each of them - Mike
  • Yeah, they were needed. There is an even better option how to display them? - RSalnikov
  • There is of course, but it is cumbersome. And with the current version it is quite possible that the optimizer recalculates the quantity when outputting each record. I would probably do it with separate requests - Mike