Upsolver
Search…
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

1
[
2
{
3
"serverIp":"10.0.0.1",
4
"time":1628894700000,
5
"cpuUsage":52.3
6
},
7
{
8
"serverIp":"10.0.0.1",
9
"time":1628894760000,
10
"cpuUsage":2.4
11
},
12
{
13
"serverIp":"10.0.0.1",
14
"time":1628894820000,
15
"cpuUsage":99.3
16
},
17
{
18
"serverIp":"10.0.0.1",
19
"time":1628894880000,
20
"cpuUsage":99.6
21
},
22
{
23
"serverIp":"10.0.0.1",
24
"time":1628894940000,
25
"cpuUsage":12.3
26
},
27
{
28
"serverIp":"10.0.0.1",
29
"time":1628895000000,
30
"cpuUsage":55
31
},
32
{
33
"serverIp":"10.0.0.2",
34
"time":1628894700000,
35
"cpuUsage":2.3
36
},
37
{
38
"serverIp":"10.0.0.2",
39
"time":1628894760000
40
},
41
{
42
"serverIp":"10.0.0.2",
43
"time":1628894820000,
44
"cpuUsage":9.3
45
},
46
{
47
"serverIp":"10.0.0.2",
48
"time":1628894880000,
49
"cpuUsage":9.6
50
},
51
{
52
"serverIp":"10.0.0.2",
53
"time":1628894940000,
54
"cpuUsage":2.3
55
}
56
]
Copied!

Query Example 1:

Count the number of entries for each severIp:
1
SET
2
DATE_TIME_UNIX = UNIX_EPOCH_TO_DATE(data.time);
3
ELECT COUNT(data.serverip) AS count_data_serverip:BIGINT, data.serverip AS serverip:STRING
4
FROM
5
"TIME_SERIES_w_NULLs"
6
GROUP BY
7
data.serverip
Copied!

Results

1
{
2
"count_data_serverip":5,
3
"serverip":"10.0.0.2"
4
}{
5
"count_data_serverip":6,
6
"serverip":"10.0.0.1"
7
}
Copied!

Query Example 2:

Count the number of entries for each time interval:
1
SET
2
Unix_Time = UNIX_EPOCH_TO_DATE(data.time);
3
SELECT
4
Unix_Time AS unix_time:STRING,
5
COUNT(Unix_Time) AS count_unix_time:BIGINT
6
FROM
7
"TIME_SERIES_w_NULLs"
8
GROUP BY
9
Unix_Time
Copied!

Results

1
{
2
"unix_time":"2021-08-13T22:47:00Z",
3
"count_unix_time":2
4
}{
5
"unix_time":"2021-08-13T22:50:00Z",
6
"count_unix_time":1
7
}{
8
"unix_time":"2021-08-13T22:48:00Z",
9
"count_unix_time":2
10
}{
11
"unix_time":"2021-08-13T22:46:00Z",
12
"count_unix_time":2
13
}{
14
"unix_time":"2021-08-13T22:45:00Z",
15
"count_unix_time":2
16
}{
17
"unix_time":"2021-08-13T22:49:00Z",
18
"count_unix_time":2
19
}
Copied!

Dialog