Your software
My Mautic version is:2.16.2
My PHP version is:7.3.31
My Database type and version is:10.4.21-MariaDB
Your problem
My problem is:
I am trying to create a report for tracking url click count by following this link (How to create reports for tracking URL Click Counts in Mautic).
I am using the group by option for ‘Hit url’ field. Upon saving the report, the report is not loaded and the console shows 500 server error for the particular report.
These errors are showing in the log:
`mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'SELECT TIMESTAMPDIFF(SECOND, ph.date_hit, ph.date_left) as data, ph.date_hit as date FROM mautic_page_hits ph LEFT JOIN mautic_pages p ON ph.page_id = p.id LEFT JOIN mautic_page_redirects r ON r.id = ph.redirect_id LEFT JOIN mautic_lead_devices ds ON ds.id = ph.device_id LEFT JOIN mautic_ip_addresses i ON i.id = ph.ip_id LEFT JOIN mautic_leads l ON l.id = ph.lead_id LEFT JOIN mautic_pages tp ON p.id = tp.id LEFT JOIN mautic_pages vp ON p.id = vp.id LEFT JOIN mautic_categories c ON c.id = p.category_id LEFT JOIN mautic_campaign_lead_event_log clel ON clel.channel="page" AND p.id = clel.channel_id AND clel.lead_id = l.id LEFT JOIN mautic_campaigns cmp ON cmp.id = clel.campaign_id WHERE (ph.date_hit IS NULL OR (ph.date_hit BETWEEN ? AND ?)) AND (ph.date_left IS NOT NULL) GROUP BY ph.url' with params ["2021-11-19 18:30:00", "2021-12-20 06:00:39"]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mykampai_mautic.ph.date_hit' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" at /home/mykampaign/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'SELECT TIMESTAMPDIFF(SECOND, ph.date_hit, ph.date_left) as data, ph.date_hit as date FROM mautic_page_hits ph LEFT JOIN mautic_pages p ON ph.page_id = p.id LEFT JOIN mautic_page_redirects r ON r.id = ph.redirect_id LEFT JOIN mautic_lead_devices ds ON ds.id = ph.device_id LEFT JOIN mautic_ip_addresses i ON i.id = ph.ip_id LEFT JOIN mautic_leads l ON l.id = ph.lead_id LEFT JOIN mautic_pages tp ON p.id = tp.id LEFT JOIN mautic_pages vp ON p.id = vp.id LEFT JOIN mautic_categories c ON c.id = p.category_id LEFT JOIN mautic_campaign_lead_event_log clel ON clel.channel=\"page\" AND p.id = clel.channel_id AND clel.lead_id = l.id LEFT JOIN mautic_campaigns cmp ON cmp.id = clel.campaign_id WHERE (ph.date_hit IS NULL OR (ph.date_hit BETWEEN ? AND ?)) AND (ph.date_left IS NOT NULL) GROUP BY ph.url' with params [\"2021-11-19 18:30:00\", \"2021-12-20 06:00:39\"]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mykampai_mautic.ph.date_hit' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /home/mykampaign/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:115, Doctrine\\DBAL\\Driver\\PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mykampai_mautic.ph.date_hit' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /home/mykampaign/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:107, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mykampai_mautic.ph.date_hit' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at /home/mykampaign/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:105)"} []`