MySQL query using the 'GROUP BY' clause

Ignition
1

Hi Guys,

SELECT * FROM recipes_data group by recipe_name,recipe_version

Above query, executed in Workbench (MySQL) by using the below solution, work perfectly.

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

But the same query does not execute in Database Query Browser ignition. Its showing below error:

image
image1015×292 19.3 KB

Any idea how to fix this issue?

Thanks,
Pritika

2

What exactly are you trying to retrieve from the database? If you want the count of versions then try this.

SELECT 
    recipe_name AS 'Recipe', 
    COUNT(recipe_version)  AS 'Versions'
FROM recipes_data 
GROUP BY recipe_name, recipe_version

(Note how a couple of line breaks make it much more readable.)

1 Like
3

You're selecting things that are not in your groups, and you need to provide some information about what to do with those. Which means an aggregation function - max, min, count, etc...

What exactly are you trying to do ?

2 Likes
4

What @pascal.fragnoud said. If your SQL is set to sql_mode=only_full_group_by then every column that is not using an aggregate function has to in your GROUP BY clause.

More info - sql - Error related to only_full_group_by when executing a query in MySql - Stack Overflow

Seems that you may have already seen this thread though given your attempt to try to change the sql mode. I would just fix the query though instead of changing the mode. It may be a little annoying and more verbose but it will also be more explicit about what is being done. If you truly don't care about what the value of a certain column is in when grouped, there is the ANY_VALUE() function.

1 Like
5

Generally, using any * wildcard in a SELECT list is fundamentally incompatible with GROUP BY. Grouping requires explicit grouping output columns and aggregate output columns, and nothing else, in every SQL dialect I know.

3 Likes