Flux Query to count unique occurrences of fields

InfluxDB 2
1

I have the following data and would like to count number of occurrences of field values. I tried different queries but cant figure out how to do it.

Data:
21.1
22.2
0
21.2
21.1
22.2
22.2
18.5
21.1
21.1

Expected Output:

21.1 4
22.2 3
0 1
18.5 1
21.2 1

Actual Output:
10

Query:
from(bucket: “my-bucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“measurment”] == “measurement_name”)
|> filter(fn: (r) => r[“_field”] == “value)
|> group(columns: [“value”], mode:“by”)
|> keep(columns: [”_value"]
|> count()

The above query counts all total number of rows in a table instead of counting unique occurences. Not sure what i am missing but need to create the table in grafana and sort based on the counts

2

Welcome @shaahul21 to the InfluxDB forum.

I think you need to use the unique() function. Maybe something like this?

from(bucket: "my-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r[“measurment”] == “measurement_name”)
  |> filter(fn: (r) => r[“_field”] == “value)
  |> unique(column: "_value")  // maybe just unique()
  |> count()
3

Hi @grant1 , Thanks for your help!

Unfortunately, the above query didn’t work. It list all the 10 rows and replaces the _value in each row as “1”

4

Hi @shaahul21

How about this?

from(bucket: "my_bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "measurement_name" and r._field == "value")
  |> group(columns: ["_value"])
  |> count(column: "_value")
  |> group()
  |> rename(columns: {"_value": "unique_value", "_count": "count"})
1 Like
5

Thanks @grant1. That query worked.

Appreciate the help!