Length

We wish to obtain the length of a column or a table (number of rows).

This section is organized as follows:

  • Element Count is concerned with one-dimensional structures such as an array or a table column. We cover the following scenarios:
    • Column: The number of elements in a table column.
    • Grouped Column: The number of elements of a column in each group.
    • Array: The number of elements in an array.
  • Row Count is concerned with tables. We cover the following scenarios:
    • Table: The number of rows in a table.
    • Grouped Table: The number of rows in each group of a grouped table.

Element Count

Column

We wish to obtain the number of elements in a column.

SELECT COUNT(col_1) col_1_length
FROM table_1;

Here is how this works:

  • To get the length of a column, we use the COUNT() function.
  • COUNT() ignores NULLs by default, if we wish to count NULLs, then we can use COUNT(1) which gives the rows count in the table.

Grouped Column

We wish to obtain the number of elements in a column for each group.

In this example, we wish to obtain the number of values of the column col_2 for each group, where the groups are defined by the values of the column col_1.

SELECT col_1,
       COUNT(col_2) col_2_length
FROM table_1
GROUP BY col_1;

Here is how this works:

The group the table by the column col_1 and use COUNT(col_2) to get the element counts of col_2. See Aggregating.

Array

We wish to obtain the number of elements in an array.

SELECT ARRAY_LENGTH([1,2,3,4]);

Here is how this works:

To get the length of an array, we use the ARRAY_LENGTH() function.

Row Count

Table

We wish to obtain the number of rows of a table.

SELECT COUNT(1) n_rows
FROM table_1;

Here is how this works:

We use the function COUNT(1) from to obtain the number of rows in a table.

Extension: Add Row Count Column

We wish to add a row to a table that has a constant value equal to the number of rows in the table.

SELECT *,
       COUNT(1) OVER () n_rows
FROM table_1;

Here is how this works:

We use COUNT() as a window function to obtain the number of rows in the table. We don't specify a PARTITION BY clause since we want this for the whole table.

Grouped Table

We wish to obtain the number of rows in each group of a grouped table.

In this example, we wish to obtain the number of rows in each group, where the groups are defined by the values of the column col_1.

SELECT col_1,
       COUNT(1) n_rows
FROM table_1
GROUP BY col_1;

Here is how this works:

We group table_1 by the column col_1 and use COUNT(1) to obtain the number of rows in the current group. See Aggregating.

SQL
I/O