Count two relationships in one MATCH separately

[igorgoldstein94]

Hello!

I have a problem when using count for a match pattern with two relationships. For example:

MATCH (a)-[r1]->(b)-[r2]->(c)
WHERE a.name = c.name AND a.name <> b.name
RETURN DISTINCT a.name AS origin, b.name AS middle, c.name AS destination,
    count(r1) AS count1, count(r2) AS count2
ORDER BY count1, count2 DESC

or even when using two separate MATCH patterns:

MATCH (a)-[r1]->(b), (c)-[r2]->(d)
WHERE ...
RETURN DISTINCT a.name AS origin, b.name AS middle1, c.name AS middle2,
    d.name AS destination, count(r1) AS count1, count(r2) AS count2
ORDER BY count1, count2 DESC

On both cases, both count1 and count2 return the same result, which is the combination of all possible relationships between the nodes (in the first query, if there are 3 relationships between a and b and 10 relationships between c and d, both counts return 30).

I'm still at the beginning of my neo4j/cypher learning curve, so I really can't figure this one out...
I hope I have made myself clear. (=

[shan · Accepted solution]

You need to use DISTINCT in your COUNT.

....
RETURN DISTINCT a.name AS origin, b.name AS middle, c.name AS destination,
    count(distinct r1) AS count1, count(distinct r2) AS count2

[igorgoldstein94]

That's exactly what I needed. Thanks!!!

[intouch_vivek]

@igorgoldstein94
I did not get your exact question however I have one question
In your first query path MATCH (a)-[r1]->(b)-[r2]->(c): flow is from a to b to c
In your second query path MATCH (a)-[r1]->(b), (c)-[r2]->(d). Cross join a to b and c to b.
Are you really feel they both are same?

[igorgoldstein94]

I didn't mean to say these queries were the same, sorry if it looked like it.
I just wanted to use two different examples of MATCH patterns that gave me the same result in terms of aggregation by count.