Aggregation Function in Apache AGE: The count() function
Tags: apache, apacheahe, postgres, database
Published: 2023-06-30 10:09:14

Apache AGE (A Graph Extension) is a powerful and efficient graph database that is built on top of PostgreSQL. It provides graph processing and querying functionalities using the Cypher query language, and one of the fundamental operations in data analysis is counting, and Apache AGE offers the `count()` function as an important tool that can be used to gather valuable insights from a graph data. 

So in this blog post, we will look at the `count()` function in Apache AGE and demonstrate how it can be used to extract meaningful information from a graph database.

---

### The Count() Function
The count() function is a function under the Aggregation functions in Apache AGE, used to return the number of occurrences of a specific pattern or element in a graph. The `count()` function returns the number of values or records, and it appears in two variants:
The `count(*)` returns the number of all matching records, while, `count(expr)` returns the number of the non-null values returned by an expression (the variable passed into the function).

In order words, by leveraging the `count()` function, you can perform aggregate calculations, filter data, and gain a deeper understanding of the structure and the intricacies of your graph.

---

**Syntax**
The `count()` function accepts an argument (`count(expression)`) and returns an integer.

### Things to consider when using the `count()` function
1. `count(*)` includes the records returning null.
2. `count(expr)` ignores the null values.
3. `count(null)` returns 0.
4. `count(*)` can be used to return the number of nodes; for example, the number of nodes connected to some node n.

### Using `count(*)` to return the number of values
**Query**

```
SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})-[]->(x)
    RETURN n.age, count(*)
$$) as (age agtype, number_of_people agtype);

    age     | no_of_people
------------+--------------
    20      | 5
(1 row)
```
In this query, The labels and the `age` property of the start node `n` and the number of nodes related to `n` are returned.

---

- We can also use `count()` to group and count relationship type. The `count()` function can be used to group relationship types and return the number.

**Query**
```
SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})-[r]->()
    RETURN type(r), count(*)
$$) as (label agtype, count agtype);

    label   | count
------------+--------------
    Knows   | 3
(1 row)
```
You can see that the relationship type and their group counts are returned.

---

### Using `count(expression)` to return the number of values
Instead of simply returning the number of records with count(*), it may be more useful to return the actual number of values returned by an expression.

**Query**
```
SELECT *
FROM cypher('graph_name', $$
    MATCH (n {name: 'A'})-[]->(x)
    RETURN count(x)
$$) as (count agtype);

   count
-----------
   3
(1 row)
```
The number of nodes connected to the start node is returned.

---

### Counting the non-null values
The `count(expression)` can be used to return the number of non-null values returned by the expression.

**Query**
```
SELECT *
FROM cypher('graph_name', $$
    MATCH (n:Person)
    RETURN count(n.age)
$$) as (count agtype);

   count
-----------
   3
(1 row)
```
In this query, you can see that the number of :Person nodes having an `age` property is returned.

---

### Conclusion
In conclusion, the `count()` function in Apache AGE is a versatile tool for analyzing graph data and extracting meaningful insights. By leveraging this function, you can obtain valuable statistics about your graph, perform aggregate calculations, and have a deeper understanding of the relationships and patterns within your data.

---

### References
- [The Aggregation functions in Apache AGE](https://age.apache.org/age-manual/master/functions/aggregate_functions.html)
- [The count() function In Apache AGE](https://age.apache.org/age-manual/master/functions/aggregate_functions.html#count)
- Visit Apache AGE Website: https://age.apache.org/
- Visit Apache AGE GitHub: https://github.com/apache/age
- Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer