Group by query with select case when gives error: Connot use non-grouped column (Canvas elasticsearch sql)

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:

Please share the expression for the error

Yes sure, this is it, il also tried select case when instead of IIF():

SELECT "shipping_country",  IIF("shipping_priority"='standard', (AVG(DATE_DIFF('days',DATE_PARSE(, 'yyyy-MM-dd HH:mm:ss.SSSSSS'), 
DATE_PARSE(, 'yyyy-MM-dd HH:mm:ss.SSSSSS')))::integer)::keyword, '-') 
AS "Avg. Standard Shipping time [in days]", 
, (AVG(DATE_DIFF('days',DATE_PARSE(, 'yyyy-MM-dd HH:mm:ss.SSSSSS'), 
DATE_PARSE(, '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 "" is not null and "shippingStatus" = 'delivered' group by "shipping_country"

Here's the result when i add "shipping_priority" in the group by, but i want to have one row per country

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

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.