How can I use group by function in neo4j?

Question

I'm new in neo4j and I don't know how I can use the 'group by' function in cypher.

I have something like this:

match(c:SEASON)<-[t:during]-(a:PLAYER)-[r:won]->(b:AWARD)
return r.year as year, t.team as team

that returns the following:

year    team

2011      OCK
2011      OCK
2011      LAS
2010      LAS
2010      SEA
2010      OCK

I would have this:

year    team     frequencies

2011     OCK        2
2011     LAS        1
2010     LAS        1
2010     SEA        1
2010     OCK        1

I know how I can do it in SQL but not in cypher. I'm interested in the team which has the highest frequency in the same year, in this case OCK in 2011.

Thanks in advance

Answer 1

Cypher doesn't have explicit group-by, instead, the grouping key is formed from the non-aggregation columns in scope. Here are the Cypher aggregation functions that produce aggregation columns.

Here's an example of use, using COUNT() as an aggregation column, making the year and team fields the grouping key implicitly:

match(c:SEASON)<-[t:during]-(a:PLAYER)-[r:won]->(b:AWARD)
return r.year as year, t.team as team, count(t.team) as frequency