Query not adding up

[moomoofarm]

Hello I'm a bit stuck in understanding this:
I have created 3 nodes: (a:Accountant) (s:Salesperson) (t:Team1).

(:Accountant {name:'Ted', age:43}),
(:Accountant {name:'Sally', age:55}),
(:Accountant {name:'Sam', age:28}),
(:Salesperson {name:'Jill', age:42}),
(:Salesperson {name:'Pam', age:34}),
(:Salesperson {name:'Bill', age:22}),
(:Salesperson {name:'Craig', age:18})
(:Team1 {color:'Blue'})

The relationship between the nodes are: (a)--[:PARTOF]-->(t)<--[:PARTOF]--(s)

I am trying to find the sum of all ages in Team1. I used this query below however, it gives me a sum total that is incorrect. The sum total of the age should be 242 but i keep getting 852. Is there something I am doing wrong?

// MATCH (a:Accountant)-[:PARTOF]->(:Team1)<-[:PARTOF]-(s:Salesperson)
RETURN sum(a.age)+sum(s.age) AS Total //

[elena_kohlwey]

Hi Josh,

the problem with your query is that you basically match all possible "paths" between accountants and salespeople. You can see this if you return a and s like

MATCH (a:Accountant)-[:PARTOF]->(:Team1)<-[:PARTOF]-(s:Salesperson)
RETURN a.name, s.name

Hence your query gives you the sum of all accountant-salesperson matches:
(43 + 42) + (43 + 34) + (43 + 22) + ... = 852
(Ted's age + Jill's age) + (Ted's age + Pam's age) + (Ted's age + Bill's age)

There are several ways of achieving what you want. I will show you some:

• You could first match all nodes from type "Accountant", sum up their ages and then do the same on salesperson:

MATCH (a:Accountant)-[:PARTOF]->(t:Team1)
WITH sum(a.age) AS accountantAge, t
MATCH (s:Salesperson)-[:PARTOF]->(t)
RETURN sum(s.age)+accountantAge AS Total

• You can make your query a bit more general:

MATCH (a)-[:PARTOF]->(t:Team1) 
WHERE a:Accountant OR a:Salesperson
RETURN sum(a.age) AS Total

Probably there are a lot more solutions. It always depends on your other nodes ;-).

Regards,
Elena

[clem]

Do you have more than one team of type :Team1 ? Your query will get all people related to :Team1!

moomoofarm:

MATCH (a:Accountant)-[:PARTOF]->(:Team1)<-[:PARTOF]-(s:Salesperson)

For just team "Blue", you want:

MATCH (a:Accountant)-[:PARTOF]->(:Team1 {color:'Blue'} )<-[:PARTOF]-(s:Salesperson)

or

MATCH (a:Accountant)-[:PARTOF]->(t:Team1)<-[:PARTOF]-(s:Salesperson)
WHERE t.color = 'Blue'

[moomoofarm]

@elena.kohlwey thanks for the response. I've been reading up on clause section of neo4j manual bits and pieces of information here and there. Your explanation helped me clear up some misunderstandings I had about path of travel.

Regarding the solution with the WITH clause provided, I just want to make sure I have the correct concept in understanding how the WITH clause actually works. (I ran it using an EXPLAIN in the beginning to see the path) What I am seeing is that we are aggregating in this case the sum of all accountant ages and passing it to the alias variable "accountantAge" which from my understanding must always follow a WITH statement in order to pass the value. After that, we are passing the variable 't' of Team1 in order for the next MATCH statement to use. Did I get it correct?

@clem haha thx. For being a beginner, I just create random data in order to test if I understand the concepts. but i'll try out that scenario.

[elena_kohlwey]

moomoofarm:

Did I get it correct?

Yes, I think you got it. The use of the variable t goes into the direction of what @clem said. If you have several teams (Team1 nodes in your case), you would want to get the overall age of one specific team or of several ones separately. The "WITH t" makes sure that in the third line you only look for all nodes that are connected to the same node that you had in your first line.