Error related to sql_mode = only_full_group_by when running MySQL query

Question

Error related to sql_mode = only_full_group_by when running MySQL query

Navigation

#1 by (1 votes)

2

I'm trying to do an ORDER BY, I have a query which shows me some products and the query works for me when my system is running, but I added an ORDER BY and the query shows me the following error, huh searched on the internet, but it is not entirely clear to me why the query does not work only by adding the ORDER BY, I show them the error and the query

In aggregate query without GROUP BY, expression # 2 of SELECT list contains nonaggregated column 'hol_products.Products.Name'; this is incompatible with sql_mode = only_full_group_by

and this is the query

SELECT COUNT(Nombre) Total, Nombre, Registro, Presentacion, ViaAdministracion, imagen, GROUP_CONCAT(Imag ORDER BY Imag desc) imag, GROUP_CONCAT(idProductos) idProductos 
FROM Productos 
INNER JOIN ProductoEspecie ON Productos.idProductos= ProductoEspecie.idProducto 
INNER JOIN especie on especie.idEspecie= ProductoEspecie.idEspecie 
INNER JOIN tipoProducto on Productos.idTipoProducto= tipoProducto.idTipo 
WHERE Productos.nombre LIKE '%lassy%' ORDER BY OrdenProductos ASC

mysql

asked by Francisco Figueroa 24.04.2018 в 00:09

source

1 answer

Problem with double php query Arraylist in java

score 1 · Accepted Answer

This happens when you mix added functions with simple fields and do not include a GROUP BY in your query. To solve it you have two options:

Add the GROUP BY clause and add all simple fields to that clause

Disable the only_full_group_by variable like this:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));