# Aggregating results by a field in chronograf

**URL:** <https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008>\
**Category:** Dashboards\
**Tags:** chronograf\
**Created:** [July 18, 2024, 2:56pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008 "2024-07-18T14:56:20Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![dani09](https://avatars.discourse-cdn.com/v4/letter/d/b782af/32.png) [@dani09](https://community.influxdata.com/u/dani09)\
**Post date:** [July 18, 2024, 2:56pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/1 "2024-07-18T14:56:20Z")

</div>

I’m a beginner in influx and have been trying to run a query with no success.

I have a measurement called “hotel\_search”. It contains a “has\_availability” tag and the following fields: hotel\_id, checkin and checkout. I’m currently trying to find the TOP unavailable hotels. The desired outcome is:

hotel\_id,unavailable\_results (amount of failed searches)  
1, 100  
2, 90  
3, 80

Initially I’ve tried to group my measurements by the hotel\_id, but it fails as it is a field.

Is there another way I can achieve this?

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [July 18, 2024, 3:01pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/2 "2024-07-18T15:01:22Z")

</div>

@dani09 Are you using Flux or InfluxQL in Chronograf?

---

<div class="post-metadata">

**Author:** ![dani09](https://avatars.discourse-cdn.com/v4/letter/d/b782af/32.png) [@dani09](https://community.influxdata.com/u/dani09)\
**Post date:** [July 18, 2024, 3:14pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/3 "2024-07-18T15:14:21Z")

</div>

@scott I’m using InfluxQL.

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [July 18, 2024, 3:28pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/4 "2024-07-18T15:28:24Z")

</div>

Ok, you’re going to need to use a [subquery](https://docs.influxdata.com/influxdb/v2/query-data/influxql/explore-data/subqueries/) here. The inner query should calculate the number of vacancies per hotel. The outer query should return the top N results of the inner query.

How do you calculate the number of vacancies per hotel? Is the `has_availability` tag scoped to each hotel or to each room?

---

<div class="post-metadata">

**Author:** ![dani09](https://avatars.discourse-cdn.com/v4/letter/d/b782af/32.png) [@dani09](https://community.influxdata.com/u/dani09)\
**Post date:** [July 18, 2024, 4:27pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/5 "2024-07-18T16:27:27Z")

</div>

@scott It’s scoped to each hotel, and it’s actually a boolean field. It indicates if the hotel has any available rooms. Unfortunately I do not know the amount of rooms per hotel.

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [July 18, 2024, 4:59pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/6 "2024-07-18T16:59:23Z")

</div>

> [@dani09](#):
>
> Unfortunately I do not know the amount of rooms per hotel.

Ok, I may have misunderstood the original question. What identifies a hotel as a “TOP unavailable hotel”? How should the hotels be sorted? In your example desired results above, what does `unavailable_results` represent and how is it calculated?

---

<div class="post-metadata">

**Author:** ![dani09](https://avatars.discourse-cdn.com/v4/letter/d/b782af/32.png) [@dani09](https://community.influxdata.com/u/dani09)\
**Post date:** [July 18, 2024, 5:42pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/7 "2024-07-18T17:42:48Z")

</div>

@scott the unavailable\_results represent the amount of points in which the has\_availability tag is 0, ie, the hotel has no available rooms. The top unavailable hotels is a list of hotels whose searches resulted in no available rooms. They should be ordered by the `unavailable_results` in a descending order

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [July 18, 2024, 5:57pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/8 "2024-07-18T17:57:16Z")

</div>

Ok, I _think_ I understand. But looking back at your original description, I want to clarify something:

> [@dani09](#):
>
> It contains a “has\_availability” tag and the following fields: hotel\_id, checkin and checkout.

`has_availability` is something I would typically store as a field, since it likely changes over time. `hotel_id` is something I’d typically store as a tag, since it’s constant and identifies where the data comes from. What do `checkin` and `checkout` represent? If these are “static” check-in and check-out times, I’d also typically store these as tags.

I’m wondering if we’re mixing up terminology or if this is the actual schema of your data. The reason I ask is because this will change how and if you can query the results you’re looking for. Just to be sure, could you provide the results from the following queries:

```sql
SHOW FIELD KEYS FROM hotel_search

```

```sql
SHOW TAG KEYS from hotel_search

```

---

<div class="post-metadata">

**Author:** ![dani09](https://avatars.discourse-cdn.com/v4/letter/d/b782af/32.png) [@dani09](https://community.influxdata.com/u/dani09)\
**Post date:** [July 18, 2024, 6:28pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/9 "2024-07-18T18:28:07Z")

</div>

@scott That’s the actual schema of my data. Actually, I’m mantaining someone else’s work, so I didn’t think much of the schema. Your points sound very valid tho. My issues running the query probably stems from the poorly designed schema then 😅

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [July 18, 2024, 7:21pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/10 "2024-07-18T19:21:35Z")

</div>

> [@dani09](#):
>
> My issues running the query probably stems from the poorly designed schema then 😅

Unfortunately, I think this is the case. In an ideal world, `hotel_id` would be a tag and you could group by it and compute a count of all rows where the `has_availability` _field_ per hotel ID. But InfluxQL can’t group by fields.

There may be a potential workaround if you’re willing/able to use Flux.

---

<div class="post-metadata">

**Author:** ![dani09](https://avatars.discourse-cdn.com/v4/letter/d/b782af/32.png) [@dani09](https://community.influxdata.com/u/dani09)\
**Post date:** [July 18, 2024, 8:00pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/11 "2024-07-18T20:00:22Z")

</div>

@scott Thanks for your help, Scott.

I’m not particularly familiar with Flux. Can you point to me which feature/concept might help me with this workaround?

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [July 18, 2024, 8:24pm UTC](https://community.influxdata.com/t/aggregating-results-by-a-field-in-chronograf/35008/12 "2024-07-18T20:24:26Z")

</div>

If you’re using InfluxDB 1.x, you’ll first need to make sure [Flux is enabled](https://docs.influxdata.com/influxdb/v1/flux/installation/). If you’re using InfluxDB 2.x, Flux is the primary query language. In Chronograf, you can use the [Flux Query Builder or Script Editor](https://docs.influxdata.com/influxdb/v1/flux/guides/flux-in-dashboards/).

Flux is essentially a functional scripting language designed specifically for querying and processing data. It gives you a lot more flexibility when you need to modify the overall structure of your data (which you do in this case).

With what I know about the schema, the Flux query would look something like this:

```javascript
from(bucket: "your-bucket-name")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "hotel_search")
    |> filter(fn: (r) => r._field == "hotel_id")
    |> map(fn: (r) => ({_time: r._time, hotel_id: r._field, unavailable_results: "tmp" }))
    |> group(columns: ["hotel_id"])
    |> count(column: "unavailable_results")
    |> group()
    |> sort(columns: ["unavailable_results"], desc: true)
    |> top(n: 5)

```
