GraphAcademy

Cypher Aggregations

Understanding Aggregation in Cypher

Working with Lists

Aggregating Functions

Adding Values

This video was recorded using an earlier version of the sandbox (Neo4j 4.x). You are using a Neo4j 5.4 sandbox for this course. The last code example has changed to add a WITH clause for sum().

Using sum() to add values

Cypher sum() returns the sum of all values retrieved during aggregation. The values must be numeric types or durations. If the numeric values are a combination of integers and floats, the result returned is a float.

Here is an example:

cypher
PROFILE MATCH (m:Movie)
WHERE m.year = 2000
RETURN sum(m.revenue) as GrossRevenue

Notice that for this Cypher code, the Movie node data is aggregated to create the sum of the revenue property values.

In this query, the value returned is of type float since at least one of the values being totaled is of type float:

cypher
WITH [1, 1.5,2] as Nums
UNWIND Nums AS  x
RETURN sum(x)

You cannot add up the elements of a list without using UNWIND to extract each element, then use sum() to total them.

Here is another example:

cypher
PROFILE MATCH (:Movie {title: 'Toy Story'})-[:IN_GENRE]->(g:Genre)<-[:IN_GENRE]-(m)
WHERE m.imdbRating IS NOT NULL
WITH g.name AS Genre,
count(m) AS MoviesInCommon,
sum(m.imdbRating) AS Total
RETURN Genre, MoviesInCommon,
Total/MoviesInCommon AS Score
ORDER By Score DESC

In this code we retrieve the number of movies Toy Story has in common with each genre name. For each movie we aggregate to create a total for the imdbRating for each movie. Then we return the genre name, the number of movies in common, and the average score. Here we determine the average by dividing the sum by the number of movies. You will learn about the Cypher avg() function in the next lesson.

Using sum() for durations

In addition to aggregating property values for nodes retrieved as a sum, you can also aggregate durations and total them. A Neo4j duration type is used to calculate the amount of time between two dates or times.

Here is an example.

cypher
MATCH (a:Actor)
WHERE
a.born IS NOT NULL
AND a.name STARTS WITH 'Tom'
WITH count(a) AS NumActors, collect(duration.between(date(a.born), date())) AS Ages
UNWIND Ages AS x
WITH NumActors, sum(x) AS TotalAges
RETURN  TotalAges, TotalAges/NumActors

In this query, we retrieve all Actor nodes with a name that begins with "Tom". We collect the durations between the birth date and now. We then UNWIND the list of durations to aggreate the values using sum(). We return the sum, but we can also use the calcuated sum to return the average age.

Check your understanding

1. What type of data can be added up?

What types of values can be added up with sum()?

  • ✓ Integers

  • ✓ Floats

  • ❏ DateTime

  • ✓ Duration

Hint

These three types of values make sense for adding their values for a calculation.

Solution

The types of values that can be used fore sum include Integers, Floats, and Durations.

2. Total of ratings

We want to return the total of the User ratings for all movies released in 2000. How do you return this data?

Use the dropdown below to select the correct code to complete this query.

Once you have selected your option, click the Check Results query button to continue.

cypher
MATCH (m:Movie)-[r:RATED]-(:User)
WHERE m.year = 2000
/*select:RETURN sum(r.rating) AS TotalRatings*/

  • ❏ RETURN total(r.rating) AS TotalRatings

  • ✓ RETURN sum(r.rating) AS TotalRatings

  • ❏ RETURN collect(r.rating) AS TotalRatings

  • ❏ RETURN collect(r.rating)++ AS TotalRatings

Hint

This function aggregates and totals numeric or duration values.

Solution

The correct answer is RETURN sum(r.rating) AS TotalRatings

Summary

In this lesson, you reviewed and learned how aggregation is used to sum data in your Cypher queries. In the next Challenge, you will create a query that totals data.