I need to count the number of connection between two nodes with a certain property

My database contains information about the nominations for the Academy 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;

which 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?

Solution

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;