Using WITH clause along with an aggregating function like count()

[clem]

I got tripped up with aggregating functions too.

My example is simpler, so it may be easier to understand what works or doesn't work and why by looking at it:

Why can't I use a conditional on an aggregating function?

One thing that's not clear is the scope of the aggregation, the grouping key is not clear if we were to use it in a WHERE clause. Would it be asking for the average of all persons who acted in all movies (so the same average would display on every result row)? Would persons be counted multiple times because they act in multiple movies, and should that duplication be captured in the average? Or is it asking for the average per movie? What if the pattern was more complicated, or there were more v…

I believe the problem you are facing, is you need to have only ONE variable that is being aggregated in the WITH statement. Then you can have one or more aggregation functions. Adding the r broke things.

Here, I'm aggregating per Person and I can get the number of movies per person or average year.

MATCH (h:Person)-[r:ACTED_IN]->(m:Movie)
WITH h, count(m) as movies, avg(m.released) AS releaseYearAvg
RETURN  h.name, movies, releaseYearAvg