MySQL 5.7: implementing GROUP BY and derived tables not returning expected results
Can someone help me understand I'm writing unit tests and I'm running into a puzzling scenario with MySQL 5.7 related to using a derived table in conjunction with a GROUP BY clause... My goal is to summarize sales data by product and month, but the results seem inconsistent. Here’s the SQL query I've constructed: ```sql SELECT product_id, MONTH(sale_date) AS sale_month, SUM(sale_amount) AS total_sales FROM ( SELECT product_id, sale_date, sale_amount FROM sales WHERE YEAR(sale_date) = 2023 ) AS sales_data GROUP BY product_id, sale_month; ``` I expect to get total sales for each product for each month of 2023. However, I'm receiving an behavior: `behavior 1055 (42000): 'sales_data.sale_month' isn't in GROUP BY` even though I am including `sale_month` in the outer query’s GROUP BY. I've tried modifying the query to use `SELECT product_id, YEAR(sale_date) AS sale_year, MONTH(sale_date) AS sale_month`, but that didn't resolve the scenario. Additionally, I've looked into MySQL's handling of `ONLY_FULL_GROUP_BY` and confirmed it’s enabled in my configuration: ```sql SHOW VARIABLES LIKE 'sql_mode'; ``` This shows `ONLY_FULL_GROUP_BY` is indeed part of the mode. Is there a specific reason why the GROUP BY is failing here, or is there a better way to structure this query to achieve my desired totals? I’m using PDO for database interactions, and the schema for the `sales` table is as follows: ```sql CREATE TABLE sales ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, sale_date DATE, sale_amount DECIMAL(10, 2) ); ``` Any insights into how to resolve this scenario or improve the query would be greatly appreciated! I appreciate any insights! This issue appeared after updating to Sql 3.10. Thanks in advance!