SQLite `GROUP BY` not aggregating correctly with custom calculation in a subquery
Could someone explain I tried several approaches but none seem to work. I'm working with an scenario with a query in SQLite where I'm trying to aggregate data using a custom calculation in a subquery. My goal is to calculate the average sale price grouped by product category but also filter out categories with total sales below a certain threshold. However, the results seem incorrect, as I get duplicate totals instead of a single aggregated result. Here's the structure of my tables: - **products** table: `id`, `category`, `price` - **sales** table: `id`, `product_id`, `quantity` I wrote the following query, expecting it to return the average sale price per category: ```sql SELECT category, AVG(price) AS average_price FROM ( SELECT p.category, p.price FROM products p JOIN sales s ON p.id = s.product_id GROUP BY p.category, p.price ) AS category_sales GROUP BY category HAVING SUM(quantity) >= 100; ``` However, instead of the average price for each category, I'm seeing multiple rows for categories that have multiple prices. I also receive the warning: `Warning: GROUP BY clause contains non-aggregated column 'category_sales.price'`, which I'm not sure how to address. I've tried modifying the inner query to exclude the price from the `GROUP BY`, but that led to an `behavior: no such column: price` when I tried to reference it in the outer query. What am I missing here? How can I correctly aggregate the average price based on the total sales quantity while adhering to the filter on the categories? I'm working on a CLI tool that needs to handle this. Any ideas what could be causing this? My team is using Sql for this CLI tool. What's the best practice here?