Skip to content

The count() function

The count() function calculates the number of the specified values or rows.

  • (nGQL-extension) You can use count() and GROUP BY together to group and count the number of specific values. Use YIELD to return.
  • (OpenCypher style) You can use count() and RETURN. GROUP BY is not necessary.

Syntax

count({expr | *})
  • count(*) returns the number of rows (including NULL).
  • count(expr) return non-NULL values return by an expression.
  • count() and size() are different.

Examples

nebula> WITH [NULL, 1, 1, 2, 2] As a UNWIND a AS b RETURN count(b), count(*), count(DISTINCT b)
+----------+----------+-------------------+
| COUNT(b) | COUNT(*) | COUNT(distinct b) |
+----------+----------+-------------------+
| 4        | 5        | 2                 |
+----------+----------+-------------------+
nebula> GO FROM "player101" OVER follow BIDIRECT YIELD $$.player.name AS Name | \
        GROUP BY $-.Name YIELD $-.Name, count(*);
+---------------------+----------+
| $-.Name             | COUNT(*) |
+---------------------+----------+
| "Dejounte Murray"   | 1        |
+---------------------+----------+
| "LaMarcus Aldridge" | 2        |
+---------------------+----------+
| "Tim Duncan"        | 2        |
+---------------------+----------+
| "Marco Belinelli"   | 1        |
+---------------------+----------+
| "Manu Ginobili"     | 1        |
+---------------------+----------+
| "Boris Diaw"        | 1        |
+---------------------+----------+

The statement in the preceding example searches for:

  • People whom player101 follows.
  • People who follow player101.

And retrieves two columns:

  • $-.Name, the names of the people.
  • COUNT(*), how many times the names show up.

Because there are no duplicate names in the basketballplayer dataset, the number 2 in the result shows that the person in that row and player101 have followed each other.

nebula> LOOKUP ON player YIELD player.age As playerage \|
 GROUP BY $-.playerage YIELD $-.playerage as age, count(*) AS number | ORDER BY number DESC, age DESC
+-----+--------+
| age | number |
+-----+--------+
| 34  | 4      |
+-----+--------+
| 33  | 4      |
+-----+--------+
| 30  | 4      |
+-----+--------+
| 29  | 4      |
+-----+--------+
| 38  | 3      |
+-----+--------+
...

nebula> MATCH (n:player) RETURN n.age as age, count(*) as number ORDER BY number DESC, age DESC
+-----+--------+
| age | number |
+-----+--------+
| 34  | 4      |
+-----+--------+
| 33  | 4      |
+-----+--------+
| 30  | 4      |
+-----+--------+
| 29  | 4      |
+-----+--------+
| 38  | 3      |
+-----+--------+

The two statements in the preceding examples retrieves the age distribution of the players in the dataset.

nebula> MATCH (v:player{name:"Tim Duncan"}) -- (v2) RETURN count(DISTINCT v2)
+--------------------+
| COUNT(distinct v2) |
+--------------------+
| 11                 |
+--------------------+
nebula> MATCH (n:player {name : "Tim Duncan"})-[]->(friend:player)-[]->(fof:player) RETURN count(fof), count(DISTINCT fof)
+------------+---------------------+
| COUNT(fof) | COUNT(distinct fof) |
+------------+---------------------+
| 4          | 3                   |
+------------+---------------------+

count(NULL)

nebula>  RETURN count(NULL), size(NULL)
+-------------+------------+
| COUNT(NULL) | size(NULL) |
+-------------+------------+
| 0           | __NULL__   |
+-------------+------------+

Last update: April 13, 2021
Back to top