InfluxDB Count with Group By field

Question

I'm a newbie in InfluxDB I have this dataset:

name: alarms
tags: zone=
time                pm25  zone
----                ----  ----
1665700728192388743 25.5  Parc de la Mar
1665733725298955656 90.75 Cas català
1665734932943043258 84.47 La Gruta
1665735530037537407 29.7  La Gruta
1665830578843393483 25.3  Parc de la Mar
1665831178926601460 29.2  Parc de la Mar
1665831778772372368 25.8  Parc de la Mar

And I need to get this result:

time count distinct
---- ----- --------
0    1     Parc de la Mar
0    1     Cas català
0    2     La Gruta
0    3     Parc de la Mar

I have tried queries like these without success:

SELECT count(distinct(zone)) FROM "alarms" GROUP BY "zone"

SELECT distinct("zone") FROM (SELECT count(zone)  FROM "alarms" GROUP BY "zone" )  

Answer 1

Count function applied non-null FIELD values instead of TAG ones according to the spec.

You could just try:

SELECT count("pm25") FROM "alarms" GROUP BY "zone"

Here is how to reproduce:

drop database test;
create database test;

INSERT alarms,zone=Parc\ de\ la\ Mar pm25=25.5 1665700728192388743
INSERT alarms,zone=Cas\ català pm25=90.75 1665733725298955656
INSERT alarms,zone=La\ Gruta pm25=84.47 1665734932943043258
INSERT alarms,zone=La\ Gruta pm25=29.7 1665735530037537407
INSERT alarms,zone=Parc\ de\ la\ Mar pm25=25.3 1665830578843393483
INSERT alarms,zone=Parc\ de\ la\ Mar pm25=29.2     1665831178926601460
INSERT alarms,zone=Parc\ de\ la\ Mar pm25=25.8 1665831778772372368

select * from alarms;

Then you should be able to see the group by results via:

SELECT count("pm25") FROM "alarms" GROUP BY "zone"