Comprehensive Guide On Using (Count) Aggregation Function In Apache AGE
Tags: postgres, apacheage
Published: 2023-05-05 02:34:56

This blog assumes you already familiar with Apache AGE extension which is an extension for PostgreSQL that adds graph database functionalities to relational database. I chose `Count` function since it is a bit tricky to a lot of developers, so by the end of this blog you should feel comfortable using it. We will start out with a simple example, then moving to a hard one.

## Setting up a database

First we need to create a test database `test_db` and create age extension for it:

```
postgres=# CREATE DATABASE test_db;
CREATE DATABASE
postgres=# \c test_db

test_db=# CREATE EXTENSION age;
CREATE EXTENSION
test_db=# LOAD 'age';
LOAD

```
data setup from [docs](https://age.apache.org/age-manual/master/functions/aggregate_functions.html#count):

```
SET search_path TO ag_catalog;

SELECT create_graph('graph_name');

SELECT * FROM cypher('graph_name', $$
	CREATE (a:Person {name: 'A', age: 13}),
	(b:Person {name: 'B', age: 33, eyes: "blue"}),
	(c:Person {name: 'C', age: 44, eyes: "blue"}),
	(d1:Person {name: 'D', eyes: "brown"}),
	(d2:Person {name: 'D'}),
	(a)-[:KNOWS]->(b),
	(a)-[:KNOWS]->(c),
	(a)-[:KNOWS]->(d1),
	(b)-[:KNOWS]->(d2),
	(c)-[:KNOWS]->(d2)
$$) as (a agtype);
```

## Simple example

Now let's start with a simple example to show the basic use of `Count` function:

```
SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    RETURN count(*)
$$) as (total agtype);
```
In this query `MATCH` will fetch all vertices in the graph, and then by the `RETURN count(*)` part we inform the query to return the count of all vertices (***** means all here). You should already be familiar with that, the result we will get in our example will be: 

```
 total
-------
 5
(1 row)
```

## Harder example

Let's move to more serious example, don't be shocked with what you will see, we will explain everything, these are the required data setup:

```
SELECT * FROM cypher('graph_name', $$
CREATE (:L {a: 1, b: 2, c: 3}),
       (:L {a: 2, b: 3, c: 1}),
       (:L {a: 3, b: 1, c: 2})
$$) as (a agtype);
```

Now let's run the following query (we will explain it down below):
```
SELECT * FROM cypher('graph_name', $$
	MATCH (x:L)
	RETURN (x.a + x.b + x.c) + count(*) + count(*), x.a + x.b + x.c
$$) as (count agtype, key agtype);
```
you should get the following output:

```
 count | key
-------+-----
 12    | 6
(1 row)
```
Let's explain What happened here, first you need to understand the concept of **grouping key**, **grouping key** is what `count` function will exactly count, `x.a + x.b + x.c` is the grouping key in this example. Let's see how see how this works in detail

```
RETURN (x.a + x.b + x.c) + count(*) + count(*), x.a + x.b + x.c
```
as `x.a + x.b + x.c` is the grouping key, we need to find how many groups it makes, according to the vertices we created above 
`(:L {a: 1, b: 2, c: 3})`,
`(:L {a: 2, b: 3, c: 1})`,
`(:L {a: 3, b: 1, c: 2})`

in all cases `(1 + 2 + 3)`, `(2 + 3 + 1)` or `(3 + 1 + 2)` they all equal **6** so they fall into the same group, which is one group: **6**, now `count` function will count how many **6** has showed up, it finds that **6** appeared **3** times, so `count(*)=3`. So if we computed left column we will get **12**:
`(x.a + x.b + x.c) + count(*) + count(*) =`
`(1 + 2 + 3) + 3 + 3 = 12`

that's why we got the output:

```
 count | key
-------+-----
 12    | 6
(1 row)
```
the right part represent the groups that can be formed which will be only one group (**6**), and the left part yields to 12 for that group!.

congratulations!, by now you should have much deeper understanding of how `count` function with **grouping keys**.