I have group by query which works fine in PHP PDO but when I add "sql_mode = ONLY_FULL_GROUP_BY" in mysql ini, the same query fails with an error
SQLSTATE[42000]: Syntax error or access violation: 1055 'db.table.t_date' isn't in GROUP BY
The actual query
SELECT
*
FROM
(
SELECT
DATE_FORMAT(t_date, '%b') AS mon,
DATE_FORMAT(t_date, '%Y-%m') AS fy_date,
(SUM(val1) - SUM(val2)) AS net_val
FROM
TABLE
GROUP BY
YEAR(t_date),
MONTH(t_date)
ORDER BY
DATE_FORMAT(t_date, '%Y-%m') DESC
LIMIT 12
) AS tbl
ORDER BY
fy_date
Can someone please tell why the above query is not working with
sql_mode = ONLY_FULL_GROUP_BY
You will need to add both date_format
expressions to the group by
because all non-aggregated columns need to be grouped.
SELECT
DATE_FORMAT(t_date, '%b') AS mon,
DATE_FORMAT(t_date, '%Y-%m') AS fy_date,
(SUM(val1) - SUM(val2)) AS net_val
FROM
TABLE
GROUP BY
DATE_FORMAT(t_date, '%b'),
DATE_FORMAT(t_date, '%Y-%m')
ORDER BY
DATE_FORMAT(t_date, '%Y-%m') DESC