SQL GROUP BY Statement - Error

Questions related to using nuBuilder Forte.

SQL GROUP BY Statement - Error

Unread postby Mr71 » Thu Sep 30, 2021 6:58 pm

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
Attachments
sql_group.PNG
sql_group.PNG (28.72 KiB) Viewed 214 times
Mr71
 
Posts: 8
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5

Re: SQL GROUP BY Statement - Error

Unread postby kev1n » Thu Sep 30, 2021 7:31 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
kev1n
nuBuilder Team
 
Posts: 2059
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: SQL GROUP BY Statement - Error

Unread postby Mr71 » Fri Oct 01, 2021 4:33 am

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!!! :roll: :D
Mr71
 
Posts: 8
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5

Re: SQL GROUP BY Statement - Error

Unread postby kev1n » Fri Oct 01, 2021 2:07 pm

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
kev1n
nuBuilder Team
 
Posts: 2059
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: SQL GROUP BY Statement - Error

Unread postby Mr71 » Sat Oct 02, 2021 5:35 pm

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

:)
Mr71
 
Posts: 8
Joined: Thu Sep 30, 2021 6:02 pm
nuBuilder Version: 4.5


Return to General

Who is online

Users browsing this forum: No registered users and 13 guests