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?




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