Cardinality function

The CARDINALITY function counts the number of elements that a collection contains. You can use the CARDINALITY function with simple or nested collections. Any duplicates in a collection are counted as individual elements. The following query shows a query that returns, for every row in the manager table, department values and the number of elements in each direct_reports collection.

Figure 1. Query

SELECT department, CARDINALITY(direct_reports) FROM manager;

Figure 2. Query result

department   marketing 5

department   engineering 7

department   publications 4

department   accounting 3

You can also evaluate the number of elements in a collection from within a predicate expression, as the following query shows.

Figure 3. Query

SELECT department, CARDINALITY(direct_reports) FROM manager
   WHERE CARDINALITY(direct_reports) < 6 
   GROUP BY department;

Figure 4. Query result

department   accounting 3

department   marketing 5

department   publications 4