CODEWITHSUNDEEP
influxdb
Vinoth Selvaraj
Vinoth Selvaraj

Reputation: 301

Influxdb: How to get count of number of results in a group by query

Is there anyway that i can get the count of total number of results / points / records in a group by query result?

> SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)

name: h2o_feet
--------------
time                   count
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
2015-08-18T00:24:00Z   2

I expect the count as 3 in this case. Even though I can calculate the number of results using the time period and interval (12m) here, I would like to know whether it is possible to do so with a query to database.

Upvotes: 3

Views: 10898

Answers (2)

shiramy
shiramy

Reputation: 833

You can use Multiple Aggregates in single query. Using your example add a select count(*) from (<inner query>):

> SELECT COUNT(*) FROM (SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m))

name: h2o_feet
--------------
time                   count_count
1970-01-01T00:00:00Z   3

However if you had a situation in which the grouping by returns empty rows, they will not be counted.

For example, counting over the below table will result in a count of 2 rather than 3:

name: h2o_feet
--------------
time                   count
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   
2015-08-18T00:24:00Z   2

To include empty rows in your count you will need to add fill(1) to your query like this:

> SELECT COUNT(*) FROM (SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m) fill(1))

Upvotes: 5

Luv33preet
Luv33preet

Reputation: 1867

You will need to do some manual work. Run it directly,

$ influx -execute "select * from measurement_name" -database="db_name" | wc -l

This will return 4 more than the actual values.

Here is an example, 

luvpreet@DHARI-Inspiron-3542:~/www$ influx -execute "select * from yprices" -database="vehicles" | wc -l
5

luvpreet@DHARI-Inspiron-3542:~/www$ influx -execute "select * from yprices" -database="vehicles" 
name: yprices
time                price
----                -----
1493626629063286219 2

luvpreet@DHARI-Inspiron-3542:~/www$

So, I think now you know why subtract 4 from the value.

Upvotes: 0

Related Questions