TopolearnMost useful answers are aggregates (top N, counts per group), and WITH is how multi-step Cypher queries are built.
Cypher has no GROUP BY. In a RETURN or WITH, any non-aggregated expressions become the grouping keys automatically:
MATCH (p:Person)<-[:FOLLOWS]-(f)
RETURN p.name, count(f) AS followersgroups by p.name. Aggregating functions include count, sum, avg, min, max, collect (build a list), percentileCont and stDev. count(*) counts rows; count(x) skips nulls; count(DISTINCT x) dedupes.
WITH ends one query part and passes chosen variables to the next, like a pipe. Use it to:
- filter on an aggregate (Cypher's equivalent of HAVING):
WITH p, count(f) AS n WHERE n > 10 - order and limit before continuing:
WITH p ORDER BY p.score DESC LIMIT 5 - compute a value once and reuse it
- drop variables you no longer need, which also limits memory use
Only variables listed in WITH survive into the next part. collect is the tool for returning one row per entity with a list of related things, which avoids the row explosion of several OPTIONAL MATCHes.
Remember aggregation happens per row of the matched patterns, so an extra MATCH that multiplies rows also multiplies counts; aggregate before expanding further.
Example
// the most followed people, with up to 3 of their followers' names
MATCH (p:Person)<-[:FOLLOWS]-(f:Person)
WITH p, count(f) AS followers, collect(f.name)[..3] AS sample
WHERE followers >= 2 // filter on the aggregate
RETURN p.name, followers, sample
ORDER BY followers DESC;
// "Bob" | 2 | ["Ann", "Cy"]
// average birth year per company (grouping key: c.name)
MATCH (p:Person)-[:WORKS_AT]->(c:Company)
RETURN c.name AS company, avg(p.born) AS avgBorn, count(*) AS staff;Remember
- Non-aggregated RETURN/WITH expressions are the grouping keys
collect()builds lists;count(DISTINCT x)dedupes- WITH pipes results between query parts and enables HAVING-style filters
- Aggregate before further MATCHes multiply rows
Watch out for
- Adding a second MATCH after counting nothing yet, then getting inflated counts from the multiplied rows.