Neo4j CQL - AGGREGATION

Like SQL, Neo4j CQL has provided some aggregation functions to use in RETURN clause. It is similar to GROUP BY clause in SQL

We can use this RETURN + Aggregation Functions in MATCH command to work on a group of nodes and return some aggregated value.

AGGREGATION Functions List

S.No.	AGGREGATION Function	Description
1.	COUNT	It returns the number of rows returned by MATCH command.
2.	MAX	It returns the maximum value from a set of rows returned by MATCH command.
3.	MIN	It returns the minimum value from a set of rows returned by MATCH command.
4.	SUM	It returns the summation value of all rows returned by MATCH command.
5.	AVG	It returns the average value of all rows returned by MATCH command.

Now we will discuss each Neo4j CQL AGGREGATION Functions in detail with examples

COUNT

It takes results from MATCH clause and counts the number of rows presents in that results and return that count value. All CQL Functions should use "( )" brackets.

Function syntax

COUNT(<value>)

NOTE -

<value> may be *, a node or relationship label name or a property name.

Example -

This example demonstrates how to use COUNT(*) function to return number of Employee nodes available in the Database.

Step 1 - Type the below command at dollar prompt in Data Browser.

MATCH (e:Employee) 
RETURN e.id,e.name,e.sal,e.deptno

Step 2 - Click on Execute button and observe the results.

We can observe that this query returns 4 rows.

Step 3 - Type the below command and click on Execute button.

MATCH (e:Employee) RETURN COUNT(*)

This query returns value 4 because Database contains 4 Employee nodes.

MAX

It takes set of rows and a <property-name> of a Node or Relationship as input and find the minimum value from the give <property-name> column of given rows.

Function syntax

MAX(<property-name> )

MIN

It takes set of rows and a <property-name> of a Node or Relationship as input and find the minimum value from the give <property-name> column of given rows.

Function syntax

MIN(<property-name> )

NOTE -

< property-name > should be name of a node or relationship.

Let us examine the MAX and MIN functions with an example.

Example -

This example demonstrates how to find the highest and lowest salary value from all Employee Nodes

Step 1 - Type the below command at dollar prompt in Data Browser.

MATCH (e:Employee) 
RETURN e.id,e.name,e.sal,e.deptno

Step 2 - Click on Execute button and observe the results.

We can observe that this query returns 4 rows.

Step 3 - Type the below command and click on Execute button.

MATCH (e:Employee) 
RETURN MAX(e.sal),MIN(e.sal)

This command finds max and min salary value from all Employee nodes available in the Database.

AVG

It takes set of rows and a <property-name> of a Node or Relationship as input and find the average value from the give <property-name> column of given rows.

Function syntax

AVG(<property-name> )

SUM

It takes set of rows and a <property-name> of a Node or Relationship as input and find the summation value from the give <property-name> column of given rows.

Function syntax

SUM(<property-name> )

Let us examine the SUM and AVG functions with an example.

Example1 -

This example demonstrates how to find the total and average salary value of all Employee Nodes

Step 1 - Type the below command at dollar prompt in Data Browser.

MATCH (e:Employee) 
RETURN e.id,e.name,e.sal,e.deptno

Step 2 - Click on Execute button and observe the results.

We can observe that this query returns 4 rows.

Step 3 - Type the below command and click on Execute button.

MATCH (e:Employee) 
RETURN SUM(e.sal),AVG(e.sal)

This command finds total and average salary value from all Employee nodes available in the Database.