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:
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:
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:
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.
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.
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.