Cypher aggregate over subset of paths

Question

I have the following graph, and am interested in getting the sum of all amounts for the leaf node, starting from a1.

However, with cypher it takes all paths into account and the end result is 24 instead of 12 using a SUM query. E.g.

match (:A {name:'a1'})<-[:BA]-(:B)-[:BC]->(c:C)-[cd:CD]->(d:D)
return d.name, SUM(cd.amount)

What should my query look like?

Here's the cypher to create the graph.

nodes:

create (a1:A {name:'a1'}), (b1:B {name:'b1'}), (b2:B {name:'b2'}),
(c1:C {name:'c1'}), (c2:C {name:'c2'}), (d1:D {name:'d1'})

rels:

match (a1:A {name:'a1'}), (b1:B {name:'b1'}), (b2:B {name:'b2'}), 
(c1:C {name:'c1'}), (c2:C {name:'c2'}), (d1:D {name:'d1'})
merge (a1)<-[:BA]-(b1)
merge (a1)<-[:BA]-(b2)
merge (b1)-[:BC]->(c1)
merge (b1)-[:BC]->(c2)
merge (b2)-[:BC]->(c1)
merge (b2)-[:BC]->(c2)
merge (c1)-[:CD {amount: 5}]->(d1)
merge (c2)-[:CD {amount: 7}]->(d1)

Try replacing "return d.name as dname, SUM(cd.amount)" with "with d.name AS dname, DISTINCT(cd) as cdd RETURN dname, SUM(cdd.amount)" — Tim Kuehn
Thanks @TimKuehn. looks like 'distinct' needs to be the first keyword after 'with'. So this works: match (:A {name:'a1'})<-[:BA]-(:B)-[:BC]->(c:C)-[cd:CD]->(d:D) with distinct d.name AS dname, cd RETURN dname, SUM(cd.amount) as total — superkruger

superkruger superkruger · Accepted Answer · 2016-05-17T13:59:46

Thanks to @TimKuehn for the 'distinct' hint.

This query works:

MATCH (:A {name:'a1'})<-[:BA]-(:B)-[:BC]->(c:C)-[cd:CD]->(d:D) 
WITH DISTINCT d.name AS dname, cd 
RETURN dname, SUM(cd.amount) as total

Cypher aggregate over subset of paths

1 Answers