# Aggregation of latest values

**URL:** <https://community.graylog.org/t/aggregation-of-latest-values/30495>\
**Category:** Graylog Central (peer support)\
**Tags:** dashboards\
**Created:** [October 29, 2023, 6:59am UTC](https://community.graylog.org/t/aggregation-of-latest-values/30495 "2023-10-29T06:59:39Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![hakan-kucuk](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/hakan-kucuk/32/15680_2.png) [@hakan-kucuk](https://community.graylog.org/u/hakan-kucuk)\
**Post date:** [October 29, 2023, 6:59am UTC](https://community.graylog.org/t/aggregation-of-latest-values/30495/1 "2023-10-29T06:59:40Z")

</div>

"Hello,

I’m new to Graylog and struggling to create a report 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:

sqlCopy code

```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 Graylog. Thank you!"

---

<div class="post-metadata">

**Author:** ![Joel\_Duffield](https://avatars.discourse-cdn.com/v4/letter/j/71c47a/32.png) [@Joel\_Duffield](https://community.graylog.org/u/Joel_Duffield)\
**Post date:** [October 29, 2023, 11:51am UTC](https://community.graylog.org/t/aggregation-of-latest-values/30495/2 "2023-10-29T11:51:14Z")

</div>

My SQL is getting rusty, can you post an example of what the aggregation should look like based on the values in your example?

---

<div class="post-metadata">

**Author:** ![hakan-kucuk](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/hakan-kucuk/32/15680_2.png) [@hakan-kucuk](https://community.graylog.org/u/hakan-kucuk)\
**Post date:** [October 29, 2023, 8:20pm UTC](https://community.graylog.org/t/aggregation-of-latest-values/30495/3 "2023-10-29T20:20:32Z")

</div>

I want to have

1. I want to get latest status for each order id
2. Then i want to count the number of orders for each latest status.  
something like  
status, count(\*)  
in\_progress, 2  
completed, 12

---

<div class="post-metadata">

**Author:** ![Joel\_Duffield](https://avatars.discourse-cdn.com/v4/letter/j/71c47a/32.png) [@Joel\_Duffield](https://community.graylog.org/u/Joel_Duffield)\
**Post date:** [October 30, 2023, 2:05pm UTC](https://community.graylog.org/t/aggregation-of-latest-values/30495/4 "2023-10-30T14:05:50Z")

</div>

So for the first part, you can do an aggregation like this:

 ![image](https://us1.discourse-cdn.com/flex016/uploads/graylog/original/2X/1/1c72fdce436ada409f63e42f3f7dfa05ea0066ab.png)  
But I think you will have to run the second one as its own widget. That would be a group by the field status, and then the metric could be count, of some field that all of them have (you can even use “timestamp”)

---

<div class="post-metadata">

**Author:** ![hakan-kucuk](https://sea2.discourse-cdn.com/flex016/user_avatar/community.graylog.org/hakan-kucuk/32/15680_2.png) [@hakan-kucuk](https://community.graylog.org/u/hakan-kucuk)\
**Post date:** [October 30, 2023, 2:28pm UTC](https://community.graylog.org/t/aggregation-of-latest-values/30495/5 "2023-10-30T14:28:46Z")

</div>

I now how to do them separately as widgets. but I don’t know how to use the first one’s result as an input to the second widget.  
Can you guide me how to do it?

---

<div class="post-metadata">

**Author:** ![Joel\_Duffield](https://avatars.discourse-cdn.com/v4/letter/j/71c47a/32.png) [@Joel\_Duffield](https://community.graylog.org/u/Joel_Duffield)\
**Post date:** [October 30, 2023, 3:54pm UTC](https://community.graylog.org/t/aggregation-of-latest-values/30495/6 "2023-10-30T15:54:44Z")

</div>

That I don’t think you can really do, widgets and the queries that run in them, are separate objects and don’t have any kind of relationship to each other. Graylog also doesn’t really have the idea of a query that feeds a query.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex016/uploads/graylog/original/3X/c/7/c7c09c6b5099570133d6502b83f50ba4430de5b6.png) [@system](https://community.graylog.org/u/system)\
**Post date:** [November 13, 2023, 3:55pm UTC](https://community.graylog.org/t/aggregation-of-latest-values/30495/7 "2023-11-13T15:55:20Z")

</div>

Closed on November 13, 2023, 3:55pm UTC

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