my Query :
select libelle,credit_initial,disponible_v,sum(montant) as montant
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee
and annee = year(current_timestamp) GROUP BY libelle order by libelle asc
I had a struggle getting this to work i've tested it and it's working on lamp server mysql version 5.12
So, steps to success:
sudo vim /etc/mysql/conf.d/mysql.cnf
Scroll to the bottom of file Copy and paste
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
to the bottom of the file
sudo service mysql restart
to restart MySQL.Done!
This worked for me:
mysql -u root -p
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
You might need sudo for the first step:
sudo mysql -u root -p
You need to specify all of the columns that you're not using for an aggregation function in your GROUP BY
clause like this:
select libelle,credit_initial,disponible_v,sum(montant) as montant
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee
and annee = year(current_timestamp) GROUP BY libelle,credit_initial,disponible_v order by libelle asc
The full_group_by
mode basically makes you write more idiomatic SQL. You can turn off this setting if you'd like. There are different ways to do this that are outlined in the MySQL Documentation. Here's MySQL's definition of what I said above:
MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)
You're getting the error because you're on a version < 5.7.5
You can disable sql_mode=only_full_group_by by some command you can try this by terminal or MySql IDE
mysql> 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';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
I have tried this and worked for me. Thanks :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With