# Aggregation of aggregation

**URL:** <https://discuss.elastic.co/t/aggregation-of-aggregation/346472>\
**Category:** Elasticsearch\
**Created:** [November 5, 2023, 6:50pm UTC](https://discuss.elastic.co/t/aggregation-of-aggregation/346472 "2023-11-05T18:50:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hakan\_Kucuk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hakan_kucuk/32/124768_2.png) [@Hakan\_Kucuk](https://discuss.elastic.co/u/Hakan_Kucuk)\
**Post date:** [November 5, 2023, 6:50pm UTC](https://discuss.elastic.co/t/aggregation-of-aggregation/346472/1 "2023-11-05T18:50:24Z")

</div>

Hello,

I’m struggling to create a query and dashboard for my specific scenario. I have a dataset of orders with the following structure:

| order\_id | order\_status | timestamp |
| --- | --- | --- |
| 1 | started | 01.01.2023 |
| 1 | in\_progress | 02.01.2023 |
| 2 | started | 02.01.2023 |
| 1 | complete | 03.01.2023 |
| 2 | in\_progress | 04.01.2023 |

What I need is the latest status of each order along with the count of those statuses. In SQL terms, the query would look like this:

```auto
WITH ranked AS (
    SELECT 
        order_id, 
        order_status, 
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY timestamp DESC) AS r
    FROM orders
)
SELECT order_status, COUNT(*) 
FROM ranked
WHERE r = 1
GROUP BY order_status;

```

I appreciate any guidance on how to achieve this in elasticsearch query and then kibana. Thank you!

---

<div class="post-metadata">

**Author:** ![Hakan\_Kucuk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hakan_kucuk/32/124768_2.png) [@Hakan\_Kucuk](https://discuss.elastic.co/u/Hakan_Kucuk)\
**Post date:** [November 7, 2023, 1:37pm UTC](https://discuss.elastic.co/t/aggregation-of-aggregation/346472/2 "2023-11-07T13:37:39Z")

</div>

any help appreciated

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 5, 2023, 1:38pm UTC](https://discuss.elastic.co/t/aggregation-of-aggregation/346472/3 "2023-12-05T13:38:02Z")

</div>

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