How to get the number of entries in a measurement

Question

I am a newbie to influxdb. I just started to read the influx documentation.

I cant seem to get the equivalent of 'select count(*) from table' to work in influx db.

I have a measurement called cart:

time                status  cartid          
1456116106077429261 0       A
1456116106090573178 0       B
1456116106095765618 0       C
1456116106101532429 0       D

but when I try to do

select count(cartid) from cart

I get the error

ERR: statement must have at least one field in select clause

score 9 · Accepted Answer · edited Mar 04 '18 at 07:56

9

I suppose cartId is a tag rather than a field value? count() currently can't be used on tag and time columns. So if your status is a non-tag column (a field), do the count on that.

EDIT:

Reference

edited Mar 04 '18 at 07:56

Zeinab Abbasimazar

9,835
23
82
131

answered Feb 22 '16 at 13:23

tkit

8,082
6
40
71

3

Correct, [InfluxDB functions](https://docs.influxdata.com/influxdb/v0.10/query_language/functions/#count) can only accept fields as arguments, not tags. – beckettsean Feb 24 '16 at 00:46

score 5 · Answer 2 · answered May 07 '20 at 04:47

This works as long as no field or tag exists with the name count:

SELECT SUM(count) FROM (SELECT *,count::INTEGER FROM MyMeasurement GROUP BY count FILL(1))

If it does use some other name for the count field. This works by first selecting all entries including an unpopulated field (count) then groups by the unpopulated field which does nothing but allows us to use the fill operator to assign 1 to each entry for count. Then we select the sum of the count fields in a super query. The result should look like this:

name: MyMeasurement
----------------
time    sum
0       47799

It's a bit hacky but it's the only way to guarantee a count of all entries when no field exists that is always present in all entries.

Very useful, here is a simpler version: `SELECT COUNT(x) FROM (SELECT *,x::INTEGER FROM MyMeasurement FILL(0))` — gatopeich, Nov 18 '22 at 10:58

Hongbo Miao · Answer 3 · 2023-04-29T07:51:53.080

If you are using InfluxDB 2, here are two more options:

Option 1 - CLI

echo 'from(bucket: "primary") |> range(start: 2010-01-01T00:00:00Z, stop: 2030-01-01T00:00:00Z) |> filter(fn: (r) => r["_measurement"] == "motor") |> count() |> yield(name: "count")' | \
influx query --org=primary -

Make sure give a big time range to include all data.

Option 2 - InfluxDB Data Explorer

from(bucket: "primary")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "motor")
  |> count()
  |> yield(name: "count")

How to get the number of entries in a measurement

3 Answers3

Option 1 - CLI

Option 2 - InfluxDB Data Explorer

Linked