phpmysqlsqlpdomysql-error-1055

Group by Query does not work in Strict ONLY_FULL_GROUP_BY SQL mode


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

Solution

  • 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