Error related to only_full_group_by when executing a query in MySql
I would just add group_id
to the GROUP BY
.
When SELECT
ing a column that is not part of the GROUP BY
there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT()
, SUM()
, MAX()
etc... I say usually because most other popular database systems insist on this. However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value! It also has an ANY_VALUE()
function that could be used as another solution to this question if you really needed the same behaviour as before. This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. MySQL are now turning on the only_full_group_by
setting by default for good reasons, so it's best to get used to it and make your queries comply with it.
So why my simple answer above? I've made a couple of assumptions:
1) the group_id
is unique. Seems reasonable, it is an 'ID' after all.
2) the group_name
is also unique. This may not be such a reasonable assumption. If this is not the case and you have some duplicate group_names
and you then follow my advice to add group_id
to the GROUP BY
, you may find that you now get more results than before because the groups with the same name will now have separate rows in the results. To me, this would be better than having these duplicate groups hidden because the database has quietly selected a value arbitrarily!
It's also good practice to qualify all the columns with their table name or alias when there's more than one table involved...
SELECT
g.group_id AS 'value',
g.group_name AS 'text'
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id
WHERE g.active = 1
AND g.department_id = 1
AND g.manage_work_orders = 1
AND g.group_name != 'root'
AND g.group_name != 'superuser'
GROUP BY
g.group_name,
g.group_id
HAVING COUNT(d.user_id) > 0
ORDER BY g.group_name
Disable ONLY_FULL_GROUP_BY
Solution 1:
Remove ONLY_FULL_GROUP_BY from mysql console
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
you can read more here
Solution 2: Remove ONLY_FULL_GROUP_BY from phpmyadmin
- Open phpmyadmin & select localhost
- Click on menu Variables & scroll down for sql mode
- Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.
Error related to only_full_group_by when executing a query in HeidiSQL
That is happening because ord_order.request_patient_id
is not a part of group by or part of a where condition. When you are doing a group by on bundleOrOrderNumber
, the patient id can assume many values. You need to do either a group by or a where condition on all the selected expressions.
Note that this only happens when a specific sql mode ONLY_FULL_GROUP_BY
. You can do a select @@sql_mode
to see what all sql modes are present. If you remove this sql mode, your query will work fine but I don't recommend that as there is a chance of the query being wrong.
Error related to only_full_group_by in MySql
You've to add all non aggregated columns in group by
SELECT s.department_id, s.category_id, s.item_id, s.item_stock_id, s.tunch, cat.category_name, im.item_name, im.stock_method, cat.category_group_id, SUM(s.grwt) AS grwt, SUM(s.ntwt) AS ntwt, sum(s.less) AS less, SUM(s.fine) AS fine
FROM item_stock s LEFT JOIN item_master im ON im.item_id = s.item_id
LEFT JOIN account pm ON pm.account_id = s.department_id
LEFT JOIN category cat ON cat.category_id = s.category_id
WHERE (im.stock_method = 1 AND (s.grwt =0 OR s.grwt !=0) OR (im.stock_method = 2 AND s.grwt != 0))AND s.department_id IN(26,27,28,29,30,31,32,59) AND s.grwt !=0 AND s.department_id = '26'
GROUP BY s.department_id, s.category_id, s.item_id, s.item_stock_id, s.tunch, cat.category_name, im.item_name, im.stock_method, cat.category_group_id
ORDER BY s.item_stock_id DESC
Error related group by when executing query in mysql
You are using a group by without aggregation function .. this behavior in mysql 5.7 is not allowed and you should unset ONLY_FULL_GROUP_BY
in sql_mode
setting .
You should esplicitally assign the columns name you need in select clause eventually use distinct (without group by) if you don't need repeated value ..
otherwise you should explicitally assign the columns and the aggregation function. Be carefull of assign the column not in aggregation function to group by clause
SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by
This
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
will be simply solved by changing the sql mode in MySQL by this command,
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
This too works for me..
I used this, because in my project there are many Queries like this so I just changed this sql mode to only_full_group_by
OR simply include all columns in the GROUP BY clause that was specified by the SELECT statement. The sql_mode can be left enabled.
Thank You... :-)
Related Topics
Are Postgresql Column Names Case-Sensitive
Postgresql Unnest() With Element Number
Count Work Days Between Two Dates
How to Select the Nth Row in a SQL Database Table
Cannot Insert Explicit Value For Identity Column in Table 'Table' When Identity_Insert Is Set to Off
MySQL Foreign Key Constraint Is Incorrectly Formed Error
How to Query SQL For a Latest Record Date For Each User
How to Use Script Variables in Psql
How to Obtain a Query Execution Plan in SQL Server
How to Use Parameters in Vba in the Different Contexts in Microsoft Access
Only Inserting a Row If It's Not Already There
How to Reset a Sequence in Oracle
Tsql Pivot Without Aggregate Function
How to Perform Grouped Ranking in MySQL
Must Appear in the Group by Clause or Be Used in an Aggregate Function