SQLite: advanced patterns with GROUP BY and string concatenation in a subquery
Hey everyone, I'm running into an issue that's driving me crazy. I'm working with a perplexing scenario when trying to aggregate data using `GROUP BY` in SQLite (version 3.36.0) combined with string concatenation in a subquery. The objective is to get a concatenated list of names grouped by a specific category. However, it seems the concatenation is not behaving as expected. Here's the SQL query I've written: ```sql SELECT category, GROUP_CONCAT(name, ', ') AS names FROM ( SELECT category, name FROM items WHERE active = 1 ) AS subquery GROUP BY category; ``` This should return a list of categories along with the names of active items in those categories. However, I am getting the following behavior message: "`no such column: name`". I have double-checked the column names in the `items` table, and `name` is indeed present. To troubleshoot, I modified the subquery to directly select from the `items` table without the `active` filter, and it worked fine. Here's the altered query: ```sql SELECT category, GROUP_CONCAT(name, ', ') AS names FROM items GROUP BY category; ``` This returns the correct results without errors. Is there a specific reason why the previous query fails? Am I not allowed to reference `name` in the `GROUP_CONCAT` function when it's in a subquery? Any insights or best practices for handling this in SQLite would be greatly appreciated. How would you solve this? This is my first time working with Sql stable.