Error with group by in mysql 5.7

Navigation
0

I have the following query : 

SELECT count(id_estadistica) AS num_clicks, a.*, b.* FROM estadisticas AS a LEFT JOIN noticias AS b ON a.id_elemento = b.id_noticia 
WHERE b.id_noticia IS NOT NULL AND b.fecha_pub BETWEEN '2017-10-01 00:00:01' AND '2017-10-31 23:59:59' 
GROUP BY a.id_elemento 
ORDER BY num_clicks 
DESC LIMIT 0, 20;

The problem is that when executing it, mysql returns this error:

  

# 1055 - Expression # 2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.id_estadistica' which is not   functionally dependent on columns in GROUP BY clause; this is   incompatible with sql_mode = only_full_group_by

I used to have version 5.5 of mysql and it worked well for me. The problem is that my server provider does not give me the option to go back in the version.

And I can not execute querys of the type: 

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Because I get this error:

  

# 1227 - Access denied. You need the SUPER privilege for this operation

Is there a way to fix it without changing the version of mysql?

    
asked by Alberto Mier 08.11.2017 в 08:16
source

1 answer

1

That query has never been able to work for you. When you use the GROUP BY clause, all the fields in the SELECT must appear in the clause GROUP BY or be within an aggregate function (such as COUNT )

If you execute the query: 

SELECT count(id_estadistica) AS num_clicks, a.id_elemento
FROM estadisticas AS a 
LEFT JOIN noticias AS b ON a.id_elemento = b.id_noticia 
WHERE b.id_noticia IS NOT NULL AND b.fecha_pub BETWEEN '2017-10-01 00:00:01' AND '2017-10-31 23:59:59' 
GROUP BY a.id_elemento 
ORDER BY num_clicks

Yes, it should work for you. If you want to add more fields in the result of the SELECT you must also add them in the clause GROUP BY .

    
answered by 08.11.2017 в 09:12
I can not disable services or close ports on ubuntu How can I remove the items from the list by clicking on the x?