Returns the number of elements in the array typed column accumulated over all rows. As such it is an aggregation function.
| ||The array column over which the function will count the elements.|
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;
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;