# Group by query with select case when gives error: Connot use non-grouped column (Canvas elasticsearch sql) **URL:** \ **Category:** Kibana\ **Tags:** elastic-stack-sql, canvas\ **Created:** [October 5, 2022, 1:01pm UTC](https://discuss.elastic.co/t/group-by-query-with-select-case-when-gives-error-connot-use-non-grouped-column-canvas-elasticsearch-sql/315880 "2022-10-05T13:01:19Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Houssam](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/houssam/32/111698_2.png) [@Houssam](https://discuss.elastic.co/u/Houssam)\ **Post date:** [October 5, 2022, 1:01pm UTC](https://discuss.elastic.co/t/group-by-query-with-select-case-when-gives-error-connot-use-non-grouped-column-canvas-elasticsearch-sql/315880/1 "2022-10-05T13:01:19Z")
Hello everyone I face a problem with a certain group by elasticsearch sql query when i try to use a field in select case condition but not group by it because it doubles my rows and leaves null values, i get the following error: ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/6/d685ba437d6e17e1b9246d9884ba1873cdecdd43.png) ---
**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\ **Post date:** [October 6, 2022, 7:53am UTC](https://discuss.elastic.co/t/group-by-query-with-select-case-when-gives-error-connot-use-non-grouped-column-canvas-elasticsearch-sql/315880/2 "2022-10-06T07:53:40Z")
Please share the expression for the error ---
**Author:** ![Houssam](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/houssam/32/111698_2.png) [@Houssam](https://discuss.elastic.co/u/Houssam)\ **Post date:** [October 6, 2022, 9:02am UTC](https://discuss.elastic.co/t/group-by-query-with-select-case-when-gives-error-connot-use-non-grouped-column-canvas-elasticsearch-sql/315880/3 "2022-10-06T09:02:46Z")
Yes sure, this is it, il also tried select case when instead of IIF(): ```auto SELECT "shipping_country", IIF("shipping_priority"='standard', (AVG(DATE_DIFF('days',DATE_PARSE(fulfilledAt.date.keyword, 'yyyy-MM-dd HH:mm:ss.SSSSSS'), DATE_PARSE(shipping_updated_at.date.keyword, 'yyyy-MM-dd HH:mm:ss.SSSSSS')))::integer)::keyword, '-') AS "Avg. Standard Shipping time [in days]", IIF("shipping_priority"='express' , (AVG(DATE_DIFF('days',DATE_PARSE(fulfilledAt.date.keyword, 'yyyy-MM-dd HH:mm:ss.SSSSSS'), DATE_PARSE(shipping_updated_at.date.keyword, 'yyyy-MM-dd HH:mm:ss.SSSSSS')))::integer)::keyword, '-') AS "Avg. express Shipping time [in days]" FROM "sbl-analytics-sales" where "shipping_country" is not null and "shipping_updated_at.date.keyword" is not null and "shippingStatus" = 'delivered' group by "shipping_country" ``` ---
**Author:** ![Houssam](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/houssam/32/111698_2.png) [@Houssam](https://discuss.elastic.co/u/Houssam)\ **Post date:** [October 6, 2022, 9:05am UTC](https://discuss.elastic.co/t/group-by-query-with-select-case-when-gives-error-connot-use-non-grouped-column-canvas-elasticsearch-sql/315880/4 "2022-10-06T09:05:05Z")
Here's the result when i add "shipping\_priority" in the group by, but i want to have one row per country ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/4/c444b5261815c75d1bd341bf2695fd3d5155d621.png) ---
**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\ **Post date:** [October 10, 2022, 7:04am UTC](https://discuss.elastic.co/t/group-by-query-with-select-case-when-gives-error-connot-use-non-grouped-column-canvas-elasticsearch-sql/315880/5 "2022-10-10T07:04:39Z")
If you don't group by it there can be multiple shipping priorities in a single row, so which one should be shown? You can for example get the very last priority using the `LAST` aggregation function: [Aggregate Functions | Elasticsearch Guide [8.4] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-aggs.html#sql-functions-aggs-last) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 7, 2022, 7:05am UTC](https://discuss.elastic.co/t/group-by-query-with-select-case-when-gives-error-connot-use-non-grouped-column-canvas-elasticsearch-sql/315880/6 "2022-11-07T07:05:23Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.