SQL GROUP BY Statement - Error

[Mr71]

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

[kev1n]

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

[Mr71]

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!!!

[kev1n]

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

[Mr71]

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