Select distinct name and count

1

Hi, I have been struggling to construct a query that identifies distinct names and count how many times each of these occurs in the the set. At the moment I do this in python, but would like to improve the queries instead.

I have two queries that identify distinct names and count the occurrences of one selected result, the struggle is to combine these two queries into one that returns both.

To count the number of times LocalOrder occurs I use the following:
select count(NAME) from Results where NAME=~ /LocalOrder/

And to identify distinct NAME’s in the measurement I use the following:
select distinct(NAME) from Results

My question is how could a single query that returns all distinct names and the number of times they occur be constructed with the help of subsets?

1 Like
2

Hello @fluxy,

I was able to do this in flux with the following script:

   from(bucket: "test")
      |> range(start: dashboardTime)
      |> filter(fn: (r) => r._field == "value")
      |> group(columns: ["_value"])
      |> map(fn: (r) => ({_time: r._time, index: 1}))
      |> cumulativeSum(columns: ["index"]) 
      |> last()

Where my data in line protocol looks like:

test,tagkey=occ, value=1
test,tagkey=occ, value=5
test,tagkey=occ, value=5
test,tagkey=occ, value=1
test,tagkey=occ, value=1

This feels a little hacky, but hopefully, it will carry you through while I look for a more elegant solution.

If you decide to explore writing more flux scripts, the spec and the testdata repo will be your best resource.