Not getting expected output for cypher query

I have a graph with 2 nodes Movie with title as attribute and Year with yearNumber as attribute and if I execute the below code

MATCH (n:Year) WHERE EXISTS(n.title) with DISTINCT count(n) as c
MATCH (n:Movie) WHERE EXISTS(n.title) RETURN DISTINCT c,count(n) as d

I get output

c d
0 10

And if I execute
MATCH (n:Movie) WHERE EXISTS(n.title) with DISTINCT count(n) as c
MATCH (n:Year) WHERE EXISTS(n.title) RETURN DISTINCT c,count(n) as d

My ouput is like
(no changes, no records)

and whynot?
c d
10 0

This is because when nothing fits a match + where, all rows get filtered out.

This is what's happening on line 1 of the first query and line 2 of the second query.

A major difference though is that a count() aggregation (alone, nothing else in scope) when there are no rows will emit a row with 0, and this is what's happening in the first query, which is correct, and leaves a row upon which the second MATCH can execute.

In the second query, the second MATCH fails wiping out the row (for the single row upon which the MATCH executes, there is no :Year node with a title property so the row is filtered out leaving no rows), so you lose that, and the count() aggregation is not alone, there's c in scope, but since there are no rows there is no possible value for c, so it can't emit a 0 for the count as any output wouldn't make sense anyway.

The approach you should take instead is to use OPTIONAL MATCH instead of MATCH if you suspect that the match may fail, but you want to get the count anyway even if it's a 0:

OPTIONAL MATCH (n:Movie) WHERE EXISTS(n.title) 
WITH count(n) as c
OPTIONAL MATCH (n:Year) WHERE EXISTS(n.title) 
RETURN c,count(n) as d
1 Like

Oh, one last thing, when you have a WITH or RETURN with an aggregation, the row becomes distinct automatically, so you can remove the DISTINCT from both lines.

Thanks for the help. :slight_smile: