COUNT

Counts the number of non-null values in a field.

Syntax

COUNT(VALUE) ‌

Arguments

VALUE: The field that you want to count. ‌

Returns

A number

Notes

Nulls are ignored

Examples

Data

[
   {
      "serverIp":"10.0.0.1",
      "time":1628894700000,
      "cpuUsage":52.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894760000,
      "cpuUsage":2.4
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894820000,
      "cpuUsage":99.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894880000,
      "cpuUsage":99.6
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894940000,
      "cpuUsage":12.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628895000000,
      "cpuUsage":55
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894700000,
      "cpuUsage":2.3
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894760000
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894820000,
      "cpuUsage":9.3
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894880000,
      "cpuUsage":9.6
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894940000,
      "cpuUsage":2.3
   }
]

Query Example 1:

Count the number of entries for each severIp:

SET
   DATE_TIME_UNIX = UNIX_EPOCH_TO_DATE(data.time);
ELECT COUNT(data.serverip) AS count_data_serverip:BIGINT, data.serverip AS serverip:STRING 
FROM
   "TIME_SERIES_w_NULLs" 
   GROUP BY
      data.serverip

Results

{
   "count_data_serverip":5,
   "serverip":"10.0.0.2"
}{
   "count_data_serverip":6,
   "serverip":"10.0.0.1"
}

Query Example 2:

Count the number of entries for each time interval:

SET
   Unix_Time = UNIX_EPOCH_TO_DATE(data.time);
SELECT
   Unix_Time AS unix_time:STRING,
   COUNT(Unix_Time) AS count_unix_time:BIGINT 
FROM
   "TIME_SERIES_w_NULLs" 
GROUP BY
   Unix_Time

Results

{
   "unix_time":"2021-08-13T22:47:00Z",
   "count_unix_time":2
}{
   "unix_time":"2021-08-13T22:50:00Z",
   "count_unix_time":1
}{
   "unix_time":"2021-08-13T22:48:00Z",
   "count_unix_time":2
}{
   "unix_time":"2021-08-13T22:46:00Z",
   "count_unix_time":2
}{
   "unix_time":"2021-08-13T22:45:00Z",
   "count_unix_time":2
}{
   "unix_time":"2021-08-13T22:49:00Z",
   "count_unix_time":2
}

Related Functions

COUNT(*) COUNT_EACH

Dialog