Given a node type ,get counts of distinct node type counts

[krishwera]

I have a graph which employee node connects to two different node types (complaints and accidents) via two different relationships like
(a:AccidentID)-[i:Involved_In]-(e:Employee)-[r:Received]-(c:ComplaintID)
I want to get both accident count and compliant count for each employee.
it is something like this.

I want to get accident count (red) =1 complaint count(orange) = 15 for kevin etc..
I tried this,
MATCH (e:Employee)-[r:Received]-(c:ComplaintID),
(e:Employee)-[i:Involved_In]-(a:AccidentID)
WITH COUNT(c:ComplaintID) AS complaint_count,COUNT(a:AccidentID) AS accident_count,COALESCE(e.first_name ,"") + ' ' + COALESCE(e.last_name ,"") AS employee_name
RETURN employee_name,complaint_count,accident_count

but it seems to manipulate accident_counts as complaint_counts.
What am i doing wrong and is there a correct way to do this

[glilienfield]

When you match both paths together, it is going to give you the Cartesian product of the results from both. In your case, you have 15 complaints and 1 accident, so you will get 15 total rows back with the single accident appended to each complaint. the count of accidents and complaints will be 15 for both, when grouping on employee.

To accomplish what you want, you need to match and count each relationship to the employee separately. The following should work, giving you a count of 1 accident and 15 complaints for kevin.

MATCH (e:Employee)-[r:Received]-(c:ComplaintID)
WITH e, count(c) as numOfComplaints
MATCH (e)-[i:Involved_In]-(a:AccidentID)
RETURN e, numOfComplaints, count(a) as numOfAccidents

[krishwera]

Thanks Gary this works! did not think it would be this simple. By any chance do you know how to include the employees with just complaints or just accidents in the query.

[glilienfield]

Your welcome. this should work:

MATCH (e:Employee)-[r:Received]-(c:ComplaintID)
WITH e, count(c) as numOfComplaints
MATCH (e)-[i:Involved_In]-(a:AccidentID)
WITH e, numOfComplaints, count(a) as numOfAccidents
WHERE numOfComplaints > 0 OR numOfAccidents > 0
RETURN e, numOfComplaints, numOfAccidents

[krishwera]

thank you for your response.
I tried this (to have 0 , i used >= sign as well) but it does not work. it gives the same results like earlier. Seems like (WHERE numOfComplaints >= 0 OR numOfAccidents >= 0) line is completely ignored. 😞

[glilienfield · Accepted solution]

I think I see the oversight in the solution. The query returns results for only those employees that have both complaints and accidents, because the two matches will not return a result if there is not a pattern to match.

To fix this, we need to use optional matches. The following code should work:

MATCH (e:Employee)
OPTIONAL MATCH (e)-[r:Received]-(c:ComplaintID)
WITH e, count(c) as numOfComplaints
OPTIONAL MATCH (e)-[i:Involved_In]-(a:AccidentID)
WITH e, numOfComplaints, count(a) as numOfAccidents
WHERE numOfComplaints > 0 OR numOfAccidents > 0
RETURN e, numOfComplaints, numOfAccidents

[krishwera]

Oh WOW ! thank you so much Gary! it works like a charm.
This will be good starting point for me to start using OPTIONAL MATCH.

Thanks again.