# MySQL query using the 'GROUP BY' clause

**URL:** <https://forum.inductiveautomation.com/t/mysql-query-using-the-group-by-clause/81149>\
**Category:** Ignition\
**Tags:** database\
**Created:** [October 30, 2023, 9:02am UTC](https://forum.inductiveautomation.com/t/mysql-query-using-the-group-by-clause/81149 "2023-10-30T09:02:26Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![PRITIKA\_VEKHANDE](https://avatars.discourse-cdn.com/v4/letter/p/71c47a/32.png) [@PRITIKA\_VEKHANDE](https://forum.inductiveautomation.com/u/PRITIKA_VEKHANDE)\
**Post date:** [October 30, 2023, 9:02am UTC](https://forum.inductiveautomation.com/t/mysql-query-using-the-group-by-clause/81149/1 "2023-10-30T09:02:26Z")

</div>

Hi Guys,

```auto
SELECT * FROM recipes_data group by recipe_name,recipe_version

```

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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/3/b3792217d92a1c3541ca393d565f233381618b51.png)

Any idea how to fix this issue?

Thanks,  
Pritika

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [October 30, 2023, 9:11am UTC](https://forum.inductiveautomation.com/t/mysql-query-using-the-group-by-clause/81149/2 "2023-10-30T09:11:51Z")

</div>

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

```auto
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.)

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [October 30, 2023, 9:13am UTC](https://forum.inductiveautomation.com/t/mysql-query-using-the-group-by-clause/81149/3 "2023-10-30T09:13:14Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [October 30, 2023, 11:47am UTC](https://forum.inductiveautomation.com/t/mysql-query-using-the-group-by-clause/81149/4 "2023-10-30T11:47:29Z")

</div>

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](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql)

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 30, 2023, 11:57am UTC](https://forum.inductiveautomation.com/t/mysql-query-using-the-group-by-clause/81149/5 "2023-10-30T11:57:01Z")

</div>

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.