nuBuilder Forum

Posted: **Thu Sep 30, 2021 11:28 am**

Hi!!
I have this problem in Nubuilder (latest release), developing this database for the music album collection.
I made a simple query to get the total number of records by music genre. If I run it on the phpMyAdmin console, it works OK !!! (in attachment)

SELECT generi.des_generi, COUNT(*) AS total
FROM generi JOIN lpcdbox ON lpcdbox.genere_lpcdbox = generi.generi_id
GROUP BY generi.des_generi ASC;

If I implement it in Nubuilder (SQL builder or manually), it gives me this incompatibility error with sql_mode. in debug.. and nothing results ..

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Sql1529862_3.lpcdbox.LPCDBOX_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

how can i solve it!?

ThAnks, Marco

Posted: **Thu Sep 30, 2021 12:01 pm**

Hi Marco

Either disable sql_mode=only_full_group_by (google if you don't know how to do it) or try wrapping your SQL statement in another SELECT * FROM ( ...

Code: Select all

SELECT * FROM (
SELECT generi.des_generi, COUNT(*) AS total
FROM generi JOIN lpcdbox ON lpcdbox.genere_lpcdbox = generi.generi_id
GROUP BY generi.des_generi ASC
) T

Posted: **Thu Sep 30, 2021 9:03 pm**

Thanks Kevin

your code works very well in PhpAdmin Console, BUT in SQL Builder Form with tablename "generi" and primary key "generi_id" don't work!!! no results (with fields "generi_des" and "total" display in browse tab: the classic list with total in order) and this is the debug message:

0] :
===USER==========

globeadmin

===PDO MESSAGE===

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'generi_id' in 'field list'

===SQL===========

SELECT generi_id,generi.des_generi,generi.total,generi.generi_id
FROM ( SELECT generi.des_generi, COUNT(*) AS total FROM generi JOIN lpcdbox ON lpcdbox.genere_lpcdbox = generi.generi_id
WHERE 1
GROUP BY generi.des_generi ASC ) T LIMIT 0, 20

===BACK TRACE====

it seems that fails to interpret the exact code (it shows a strange sintax)..... but I can be wrong!!!

Nubuilder it's a great platform, you can do anything, but the grouping for statistics I don't understand why it doesn't want to do it!!!

Posted: **Fri Oct 01, 2021 6:37 am**

Try this:

Code: Select all

SELECT * FROM (
SELECT NULL as generi_id, generi.des_generi, COUNT(*) AS total
FROM generi JOIN lpcdbox ON lpcdbox.genere_lpcdbox = generi.generi_id
GROUP BY generi_id, generi.des_generi ASC
) T

Posted: **Sat Oct 02, 2021 10:05 am**

thank you very much Kevin,
this solution seems to work very well. Now I try to implement it for the other statistics.

nuBuilder Forum

SQL GROUP BY Statement - Error

SQL GROUP BY Statement - Error

Re: SQL GROUP BY Statement - Error

Re: SQL GROUP BY Statement - Error

Re: SQL GROUP BY Statement - Error

Re: SQL GROUP BY Statement - Error