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

[Houssam]

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:

[flash1293 (Joe Reuter)]

Please share the expression for the error

[Houssam]

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(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"

[Houssam]

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

[flash1293 (Joe Reuter)]

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