COUNT query help

I have a time series called ping with a tag for room name, and I want to build a Grafana stat visualization that shows a count of how many devices in each room are unhealthy (i.e. result_code < 1).

In PromQL, I can achieve it like this:

count (ping{site="$site", room="$room"}<1) OR on() vector(0)

I set the stat visualization to repeat by room variable and set that to “All”:

Could someone help me figure out how to accomplish the same thing in InfluxQL? Below is a sample of the data, exported as CSV from Influx Data Explorer:

name ,tags ,time                ,result_code ,room            ,name
ping ,     ,1705971963000000000 ,          0 ,The Jar         ,UPS001
ping ,     ,1705971963000000000 ,          0 ,Holodeck Lounge ,USW001
ping ,     ,1705972023000000000 ,          0 ,Home Theater    ,DSP001
ping ,     ,1705972023000000000 ,          0 ,The Jar         ,PC002
ping ,     ,1705972023000000000 ,          0 ,The Jar         ,PC003

Hello @llamafilm,
I believe the query you’re looking for would be something like:

SELECT count("result_code") AS "count_result" FROM "<measurement name>" WHERE "count_result" >1 GROUP BY "room"

Let me know if that works. MY influxql is a bit rusty lol

Thanks, but that query returns no results.