"SQL Table" Data processor source add a field in select: SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_table.id'
I would like to report the sum of last year's donations whose financial type is deductible.
In Data Processor Source I added "SQL Table" civicrm_financial_type
:
All columns except total_amount are aggregated: contact_id, first_name, last_name.
But the generated query add sql_table.id
column in select and I have an error:
SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
There is the generated query:
SELECT DISTINCT
`contribution`.`contact_id` AS `id_contatto`,
SUM( `contribution`.`total_amount` ) AS `totale_donazioni`,
==`sql_table`.`id` AS `sql_tableid`,==
`contatto`.`first_name` AS `nome`,
`contatto`.`last_name` AS `cognome`
FROM
`civicrm_contribution` `contribution`
INNER JOIN ( SELECT * FROM `civicrm_financial_type` WHERE is_deductible = 1 ) AS `sql_table` ON `sql_table`.`id` = `contribution`.`financial_type_id`
INNER JOIN `civicrm_contact` `contatto` ON `contatto`.`id` = `contribution`.`contact_id`
AND ( `contatto`.`is_deleted` = 0 )
LEFT JOIN `civicrm_value_altri_dati_co_1` `contatto_Altri_dati_contatto` ON `contatto`.`id` = `contatto_Altri_dati_contatto`.`entity_id`
WHERE
1
AND `contribution`.`is_test` = 0
AND `contribution`.`contribution_status_id` IN ( 1 )
AND `contribution`.`receive_date` BETWEEN 20220101000000
AND 20221231235959
AND `contatto`.`contact_type` = 'Individual'
GROUP BY
`contribution`.`contact_id`,
`contatto`.`first_name`,
`contatto`.`last_name`
ORDER BY
`id_contatto`
I have to add 'sql_table.id' as Data Processor Field:
but if a contact make contributions with different financial types I'll have more than one row.