Query to count occurrences of distinct values



I could not find the way to build a single query for getting the count of occurrences of distinct values.

select distinct(allocationLimit) from resources
name: resources
time distinct

0 54636
0 13220
0 0
0 14098

select count(allocationLimit) from resources where allocationLimit=54636
name: resources
time count

0 2

Like in the above example, how can I get the occurrences of all distinct values of “allocationLimit” in single query with output like:

Count distinct

2 54636
1 13220
4 0
5 14098

Any idea on how to do this?


1 Like
Query a counter of distinct values


Have you tried this request ?

SELECT count(allocationLimit) FROM resources GROUP BY allocationLimit



Did you figure this out? GROUP BY doesn’t work on fields, and in my case I also need to GROUP BY a tag.

I’m storing data about computers. One field I have is the OS version, which I don’t want to store as a TAG since it will change often. I tag by serial number so my series will look like this:

tags { serial: x1234 }
fields { os_version: “Mac OSX 10.12.5” }

I can do this to get a list of distinct versions, I want the last known value.
select distinct(“last”) from (SELECT last(“os_version”) FROM “computers” GROUP BY serial)

name: computers
time distinct

0 Mac\ OS\ X\ 10.12.6\ (16G1114)
0 Mac\ OS\ X\ 10.12.6\ (16G1036)
0 Mac\ OS\ X\ 10.13.2\ (17C205)
0 Mac\ OS\ X\ 10.12.6\ (16G29)

But how do I count the number of each distinct version? Like OP, I want:

name: compuers
count distinct

200 Mac\ OS\ X\ 10.12.6\ (16G1114)
450 Mac\ OS\ X\ 10.12.6\ (16G1036)
100 Mac\ OS\ X\ 10.13.2\ (17C205)
600 Mac\ OS\ X\ 10.12.6\ (16G29)


No I haven’t found any solution yet as GROUP BY supports only tags and time


Hello @ashinde,

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.