Using SUM and LATEST in ESQL

Elastic Stack Kibana
1

I have log data that contains the name and price of an item purchased.
For example:

.fruit-cost

Date, Name, Price
2024-03-01 12:00, Banana, 1.05
2024-03-01 11:00, Banana, 1.07
2024-03-01 11:30, Strawberry, 0.97

I would like to use ES|QL to calculate both the latest and total cost spend on each item.

FROM fruit-cost
| STATS total_cost = SUM(price) BY name

Gives me the total cost, but how to I fetch the latest price of each item?

Thanks,
Liza

2

Hi @lizozom

I think this more an Elasticsearch question, but AFAIK that is not possible yet to do in ES|QL.
I think this issue is sounds similar of your request: ESQL: Aggregations feature requests · Issue #99999 · elastic/elasticsearch · GitHub

3

Thanks @Marco_Liberati .
This is exactly what I'm after. Curious to see when this ends up on the roadmap, because currently the only alternative is to run a Transform and then ESQL on top of it.