Italian Raggruppare i dati e visualizzarli in un grafic

Nikeddy

Active Member
Licensed User
Signori, ho ripreso questo vecchio post : https://www.b4x.com/android/forum/threads/creare-un-grafico-con-b4a.77369/

e l'ho riprodotto: ora mi da errore, come mai?
B4X:
ResponseError. Reason: , Response: SELECT MONTHNAME(Data) As mese, SUM(incasso) As totale FROM incassi WHERE n_macchina = 3 GROUP BY MONTH(Data)\nExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxdb.incassi.data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT MONTHNAME(Data) As mese, SUM(incasso) As totale FROM incassi WHERE n_macchina = 3 GROUP BY MONTH(Data)\nExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxdb.incassi.data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
 

MarcoRome

Expert
Licensed User
Longtime User
#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxdb.incassi.data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Leggi: https://medium.com/@kasunsiyambalap...mode-only-full-group-by-in-mysql-de811ed35ae9

prima della query esegui:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

per approfondimenti leggi: https://stackoverflow.com/questions/23921117/disable-only-full-group-by
 

LucaMs

Expert
Licensed User
Longtime User
Credo che in SQLite (che penso tu stia usando) non supporti i nomi dei mesi.

Una query come questa dovrebbe funzionare:

SELECT strftime('%m', Data) As mese, SUM(incasso) As totale FROM incassi WHERE n_macchina = 3 GROUP BY mese
 

Nikeddy

Active Member
Licensed User
purtroppo non è possibile modificare le impostazioni del server MySQL, vanno sistemate le query dello script...

come raggruppo?
 

Nikeddy

Active Member
Licensed User
niente ho questo problema tra group by e only_full_group_by
che non so nemmeno come risolvere..
 

Star-Dust

Expert
Licensed User
Scusami non ho seguito, cosa devi ottenere?
 

Nikeddy

Active Member
Licensed User
provo a spiegare un passo alla volta, in primis dal db faccio questa query

B4X:
SELECT MONTHNAME(Data) As mese, SUM(Importo) As totale FROM incassi WHERE n_macchina = 1 GROUP BY MONTH(Data)

cosi dovrei avere gli incassi divisi per mese.

il primo problema e' di sql

B4X:
ResponseError. Reason: , Response: SELECT MONTHNAME(Data) As mese, SUM(Importo) As totale FROM incassi WHERE n_macchina = 1 GROUP BY MONTH(Data)\nExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'manzuoli14424.incassi.Data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT MONTHNAME(Data) As mese, SUM(Importo) As totale FROM incassi WHERE n_macchina = 1 GROUP BY MONTH(Data)\nExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'manzuoli14424.incassi.Data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
 

Star-Dust

Expert
Licensed User
Mi pare di aver capito, con il mio scarso inglese, che non raggruppa per una funzione di un campo, ma eventualmente per campo
Giusto?

Data in che formato é?
long (tipo Tick)
Integer (Tipo AAAAMMGG)
Tipo string

Funzionerebbe senza GROUP?
PHP:
SELECT MONTHNAME(Data) As mese, SUM(Importo) As totale FROM incassi WHERE n_macchina = 1
 

Nikeddy

Active Member
Licensed User
B4X:
SELECT MONTHNAME(Data) As mese, SUM(Importo) As totale FROM incassi WHERE n_macchina = 2\nIn aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'manzuoli14424.incassi.Data'; this is incompatible with sql_mode=only_full_group_by

no...
 

Star-Dust

Expert
Licensed User
B4X:
SELECT MONTHNAME(Data) As mese, SUM(Importo) As totale FROM incassi WHERE n_macchina = 2\nIn aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'manzuoli14424.incassi.Data'; this is incompatible with sql_mode=only_full_group_by

no...
Perché non riconosce la funzione MONTNAME o la dichiarazione As Mese?
Cosi funziona?
B4X:
SELECT Data, SUM(Importo) FROM incassi WHERE n_macchina = 1

la data é VarChar(16) quindi come sarebbe memorizzata la data di oggi ?
2018-04-19 16:22
 

Nikeddy

Active Member
Licensed User
niente, stesso errore, mi sa che mi conviene cambiare server sql, cosi da avere piu' possibilita' di essere elastico.
 

Star-Dust

Expert
Licensed User
prova cosi

SELECT Data, SUM(Importo) FROM incassi WHERE n_macchina = 1 GROUP BY SUBSTRING(Data, 4, 2)
 

Star-Dust

Expert
Licensed User
niente, stesso errore, mi sa che mi conviene cambiare server sql, cosi da avere piu' possibilita' di essere elastico.
la query é semplice, non può dare errore ci sarà un altro motivo

Che errore da adesso
 

Nikeddy

Active Member
Licensed User
SELECT Data, SUM(Importo) FROM incassi WHERE n_macchina = 2 GROUP BY SUBSTRING(Data,4,2)\nExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'manzuoli14424.incassi.Data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
 
Top