Get count of each OPTIONAL MATCH

Question

Trying to run the following query:

MATCH(u:User) 
OPTIONAL MATCH (u)-[:USER_TYPE]->(adminType:UserType {type:'Admin'}) 
OPTIONAL MATCH (u)-[:USER_TYPE]->(userType:UserType {type:'User'}) 
RETURN DISTINCT { adminCount: count(adminType), userCount: count(userType) }

This returns me the count of the admins, but the users are 0. If I switch the OPTIONAL MATCH and set the user first, then I get a count for the user, but not the admin. How do I get of both?

I thought my cypher was pretty easy to understand. Just trying to get the count of each user type. — Sonu Kapoor
It did not look like that was what you are trying to do, since you are including the User nodes in your query. To get the counts of each type, you just need to do something like the following (without involving User nodes at all): MATCH (adminType:UserType {type:'Admin'}) WITH count(*) AS adminCount MATCH (userType:UserType {type:'User'}) RETURN {adminCount: adminCount, userCount: COUNT(*)} — cybersam
I see where the confusion was.Thanks for sharing your solution. — Sonu Kapoor

William Lyon William Lyon · Accepted Answer · 2015-12-17T18:57:06

Instead of using OPTIONAL MATCH try this:

MATCH (u:User)-[:USER_TYPE]->(adminType:UserType {type:'Admin'})
WITH count(*) AS adminCount
MATCH (u:User)-[:USER_TYPE]->(userType:UserType {type:'User'})
WITH count(*) AS userCount, adminCount
RETURN {adminCount: adminCount, userCount: userCount}

Match on each pattern then count the number of matches using a WITH clause to bring through only the count.

Edit

As pointed out by @cybersam the above query takes into account the number of relationships, to get the count of UserType nodes with type property values of "Admin" and "User" (without taking relationships into account):

MATCH (adminType:UserType {type:'Admin'}) WITH count(adminType) AS adminCount
MATCH (userType:UserType {type:'User'}) WITH adminCount, count(userType)
RETURN {adminCount: adminCount, userCount: userCount}

Get count of each OPTIONAL MATCH

1 Answers