I need to count the number of connection between t

Question

My database contains informations about the nominations for the accademy awards.

I want to know how many directors have won an oscar for "best director" more than one time.

I can't quite get to the result that i want, a list of nominees. The closest I've been is with this query:

MATCH (n:Nominee)-[n1:NOMINATED]->(c:Category)
WHERE c.name="Best Director" AND n1.win=true
RETURN count(n1.win), n.name
ORDER BY n.name;

wich returns the directors names and the number of times they won an oscar.

I tried to do something like

MATCH (n:Nominee)-[n1:NOMINATED]->(c:Category)
WHERE c.name="Best Director" AND n1.win=true AND count(n1.win)>1
RETURN n.name;

but got an error that says

Invalid use of aggregating function count(...) in this context (line 2, column 50 (offset: 96)) "WHERE c.name="Best Director" AND n1.win=true AND count(n1.win)>1"

Can someone help me with this?

Answer 1

Use WITH to aggregate the wins first. According to the docs:

[...] WITH is used to introduce aggregates which can then by used in predicates in WHERE. These aggregate expressions create new bindings in the results. WITH can also, like RETURN, alias expressions that are introduced into the results using the aliases as binding name.

So a query like this should work:

MATCH (n:Nominee)-[n1:NOMINATED]->(c:Category)
WHERE c.name="Best Director" AND n1.win=true
WITH n, count(n1.win) AS winCount
WHERE winCount > 1
RETURN n.name;

See also the docs on WHERE:

WHERE adds constraints to the patterns in a MATCH or OPTIONAL MATCH clause or filters the results of a WITH clause.