ARRAY_COUNT_GLOBAL
Returns the number of elements in the array typed column accumulated over all rows. As such it is an aggregation function.
Syntax
ARRAY_COUNT_GLOBAL(<array>)
Parameter | Description |
---|---|
<array> | The array column over which the function will count the elements. |
Example
For this example, we will create a table array_test
as shown below.
CREATE DIMENSION TABLE array_test(array_1 ARRAY(INTEGER));
INSERT INTO
array_test
VALUES
([ 1, 2, 3, 4 ]),
([ 5, 0, 20 ]),
([ 6, 2, 6 ]),
([ 9, 10, 13 ]),
([ 20, 13, 40 ]),
([ 1 ]);
We can use ARRAY_COUNT_GLOBAL
to learn how many total array elements are in all rows.
SELECT
ARRAY_COUNT_GLOBAL(array_1)
FROM
array_test;
Returns: 17
If you want to count elements based on specific criteria, you can use the ARRAY_COUNT
function with a SUM
aggregation as demonstrated below.
SELECT
SUM(ARRAY_COUNT(x -> x > 3, array_1))
FROM
array_test;
Returns: 11