Upsolver
Search…
COUNT_DISTINCT
Counts the number of distinct values that appear in a field.

Syntax

COUNT_DISTINCT(VALUE)

Arguments

VALUE: The field you want to count. ‌

Returns

An integer

Notes

Duplicate field values are not counted. For example: for values: "a, b, c, c, c, b" the distinct count would be 3, because the values "b" and "c" repeat and only get counted once. NULLs are not counted.

Example

Data

1
[
2
{
3
"GROUP_ID":"G1",
4
"USER_ID":"U1",
5
"CONNECTION_TIME":"2020-06-26 02:31:29,573"
6
},
7
{
8
"GROUP_ID":"G1",
9
"USER_ID":"U2",
10
"CONNECTION_TIME":"2020-06-26 18:11:45,783"
11
},
12
{
13
"GROUP_ID":"G2",
14
"USER_ID":"Z1",
15
"CONNECTION_TIME":"2020-06-26 23:54:27,687"
16
},
17
{
18
"GROUP_ID":"G2",
19
"CONNECTION_TIME":"2020-07-26 23:54:27,687"
20
},
21
{
22
"GROUP_ID":"G1",
23
"USER_ID":"U2",
24
"CONNECTION_TIME":"2021-07-01 02:31:29,573"
25
},
26
{
27
"GROUP_ID":"G1",
28
"USER_ID":"U1",
29
"CONNECTION_TIME":"2021-07-01 18:11:45,783"
30
},
31
{
32
"GROUP_ID":"G2",
33
"USER_ID":"Z1",
34
"CONNECTION_TIME":"2021-07-01 23:54:27,687"
35
}
36
]
Copied!

Query Example 1:

Count the number of distinct entries for each unique entry in a field:
1
SELECT
2
COUNT(DISTINCT data.USER_ID) AS count_distinct_data_user_id:BIGINT,
3
data.GROUP_ID AS group_id:STRING
4
FROM
5
"SAMPLE_DATA_G1U1 - json"
6
GROUP BY
7
data.GROUP_ID
Copied!

Results:

1
{
2
"count_distinct_data_user_id":1,
3
"group_id":"G2"
4
}{
5
"count_distinct_data_user_id":2,
6
"group_id":"G1"
7
}
Copied!
COUNT

Dialog

Last modified 8mo ago