Hello,
I try to create my first email-reports in mautic.
I create a report for send emails like this:
With this settings it works and I can see the results of the report.
But when I change the report and add something like the send-date or the campaign name to the columns the report is not working anymore and I get an error like this in the log:
[2022-10-13 19:58:57] mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'SELECT count(*) FROM (SELECT `e`.`id` AS e_id, `e`.`subject` AS subject1, `es`.`date_sent` AS date_sent, COUNT(e.sent_count) AS 'COUNT e.sent_count', SUM(es.is_read) AS 'SUM es.is_read', SUM(IF(cut.hits is NULL, 0, 1)) AS 'SUM is_hit', SUM(IFNULL(cut.unique_hits, 0)) AS 'SUM unique_hits', SUM(IF(dnc.id IS NOT NULL AND dnc.reason=1, 1, 0)) AS 'SUM unsubscribed', SUM(IF(dnc.id IS NOT NULL AND dnc.reason=2, 1, 0)) AS 'SUM bounced', SUM(es.viewed_in_browser) AS 'SUM es.viewed_in_browser' FROM email_stats es LEFT JOIN emails e ON e.id = es.email_id LEFT JOIN lead_donotcontact dnc ON es.email_id = dnc.channel_id AND dnc.channel='email' AND es.lead_id = dnc.lead_id LEFT JOIN (SELECT COUNT(ph.id) AS hits, COUNT(DISTINCT(ph.redirect_id)) AS unique_hits, cut2.channel_id, ph.lead_id FROM channel_url_trackables cut2 INNER JOIN page_hits ph ON cut2.redirect_id = ph.redirect_id AND cut2.channel_id = ph.source_id WHERE (cut2.channel = 'email' AND ph.source = 'email') AND (cut2.channel_id IN (?)) GROUP BY cut2.channel_id, ph.lead_id) cut ON es.email_id = cut.channel_id AND es.lead_id = cut.lead_id WHERE (es.date_sent IS NULL OR (es.date_sent BETWEEN ? AND ?)) AND (e.id = 500) GROUP BY e.id) c' with params ["500", "2022-08-01 00:00:00", "2022-10-13 19:58:57"]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mautic.es.date_sent' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" at /var/www/mailer.promotionbasis.de/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'SELECT count(*) FROM (SELECT `e`.`id` AS e_id, `e`.`subject` AS subject1, `es`.`date_sent` AS date_sent, COUNT(e.sent_count) AS 'COUNT e.sent_count', SUM(es.is_read) AS 'SUM es.is_read', SUM(IF(cut.hits is NULL, 0, 1)) AS 'SUM is_hit', SUM(IFNULL(cut.unique_hits, 0)) AS 'SUM unique_hits', SUM(IF(dnc.id IS NOT NULL AND dnc.reason=1, 1, 0)) AS 'SUM unsubscribed', SUM(IF(dnc.id IS NOT NULL AND dnc.reason=2, 1, 0)) AS 'SUM bounced', SUM(es.viewed_in_browser) AS 'SUM es.viewed_in_browser' FROM email_stats es LEFT JOIN emails e ON e.id = es.email_id LEFT JOIN lead_donotcontact dnc ON es.email_id = dnc.channel_id AND dnc.channel='email' AND es.lead_id = dnc.lead_id LEFT JOIN (SELECT COUNT(ph.id) AS hits, COUNT(DISTINCT(ph.redirect_id)) AS unique_hits, cut2.channel_id, ph.lead_id FROM channel_url_trackables cut2 INNER JOIN page_hits ph ON cut2.redirect_id = ph.redirect_id AND cut2.channel_id = ph.source_id WHERE (cut2.channel = 'email' AND ph.source = 'email') AND (cut2.channel_id IN (?)) GROUP BY cut2.channel_id, ph.lead_id) cut ON es.email_id = cut.channel_id AND es.lead_id = cut.lead_id WHERE (es.date_sent IS NULL OR (es.date_sent BETWEEN ? AND ?)) AND (e.id = 500) GROUP BY e.id) c' with params [\"500\", \"2022-08-01 00:00:00\", \"2022-10-13 19:58:57\"]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mautic.es.date_sent' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/mailer.promotionbasis.de/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mautic.es.date_sent' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/mailer.promotionbasis.de/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mautic.es.date_sent' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /var/www/mailer.promotionbasis.de/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:112)"} []
Hope someone can help me with this.
Best regards,
Timo