multi_distinct_sum
Description
Returns the sum of distinct values in expr
, equivalent to sum(distinct expr).
Syntax
multi_distinct_sum(expr)
Parameters
expr
: the columns involved in the calculation. Column values can be of the following types: TINYINT, SMALLINT, INT, LARGEINT, FLOAT, DOUBLE, or DECIMAL.
Return value
The mapping between column values and return value types is as follows:
- BOOLEAN -> BIGINT
- TINYINT -> BIGINT
- SMALLINT -> BIGINT
- INT -> BIGINT
- BIGINT -> BIGINT
- FLOAT -> DOUBLE
- DOUBLE -> DOUBLE
- LARGEINT -> LARGEINT
- DECIMALV2 -> DECIMALV2
- DECIMAL32 -> DECIMAL128
- DECIMAL64 -> DECIMAL128
- DECIMAL128 -> DECIMAL128
Examples
-
Create a table with
k0
as the INT field.CREATE TABLE tabl
(k0 BIGINT NOT NULL) ENGINE=OLAP
DUPLICATE KEY(`k0`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`k0`) BUCKETS 1
PROPERTIES(
"replication_num" = "3",
"storage_format" = "DEFAULT"
); -
Insert values into the table.
INSERT INTO tabl VALUES ('0'), ('1'), ('1'), ('1'), ('2'), ('2');
-
Use multi_distinct_sum() to calculate the sum of distinct values in the
k0
column.MySQL > select multi_distinct_sum(k0) from tabl;
+------------------------+
| multi_distinct_sum(k0) |
+------------------------+
| 3 |
+------------------------+
1 row in set (0.03 sec)The distinct values of
k0
is 0, 1, 2 and we can get 3 after adding them together.