Query to count occurrences of distinct values


#1

Hi,

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?

Thanks,
Arti


#2

Hey,

Have you tried this request ?

SELECT count(allocationLimit) FROM resources GROUP BY allocationLimit

Bugs’


#3

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)


#4

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