MySQL: advanced patterns with GROUP BY and Aggregated Functions in Subqueries
After trying multiple solutions online, I still can't figure this out... I've looked through the documentation and I'm still confused about I'm working with an scenario while trying to use a subquery with `GROUP BY` in MySQL 8.0. I want to retrieve the average purchase amount for each customer from the `orders` table, but I seem to be getting incorrect results. Here's a simplified version of my query: ```sql SELECT customer_id, (SELECT AVG(amount) FROM orders o WHERE o.customer_id = c.customer_id) AS avg_purchase FROM customers c GROUP BY customer_id; ``` I expected to see the average purchase amount for each customer, but instead, I'm getting the following behavior: ``` behavior 1055 (42000): 'mydb.customers.customer_id' isn't in GROUP BY ``` I tried adding `customer_id` to the `GROUP BY` clause, yet the results still don't match my expectations. When I run the subquery independently, it returns the correct average. Here’s the result of the subquery: ```sql SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id; ``` This works fine, but when embedded in the main query, it causes issues. I've also considered using a `JOIN` instead of a subquery: ```sql SELECT c.customer_id, AVG(o.amount) AS avg_purchase FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id; ``` This seems to work, but I prefer the subquery for readability. Is there a proper way to use a subquery with `GROUP BY` that avoids the behavior, or should I stick with the `JOIN` approach? Any insights would be appreciated! Thanks in advance! My development environment is Linux. How would you solve this? This issue appeared after updating to Sql stable. This issue appeared after updating to Sql 3.10. How would you solve this?