How to use SQL-like GROUP BY in Cypher query langu

问题:

I want to find the number of all users in a company and the number of its men and women. My query is:

 start n=node:company(name:"comp")
 match n<-[:Members_In]-x, n<-[:Members_In]-y  
 where x.Sex='Male' and y.Sex='Female' 
 return n.name as companyName, count(distinct x) as NumOfMale,
 count(distinct y) as NumOfFemale" );

My query is correct, but I know I shouldn't use n<-[:Members_In]-y in the match clause.

How can I get the number of male, number of female, and total number of users?

回答1:

Peter creates a cross product here:

I think this works better for your use-case

start n=node:node_auto_index(name='comp')
match n<-[:Members_In]-x
with  n.name as companyName, collect(x) as employees
return length(filter(x in employees : x.Sex='Male')) as NumOfMale,
length(filter(x in employees : x.Sex='Female')) as NumOfFemale,
length(employees) as Total

see http://console.neo4j.org/r/msamaa

回答2:

Try

start n=node:node_auto_index(name='comp')
match n<-[:Members_In]-x, n<-[:Members_In]-y  
where x.Sex='Male' and y.Sex='Female' 
with 
n.name as companyName, 
count(distinct x) as NumOfMale,
count(distinct y) as NumOfFemale
return NumOfMale, NumOfFemale, NumOfMale + NumOfFemale as Total

See http://tinyurl.com/cjpxrax for an example.

回答3:

There is actually an even easier way to achieve that result

START n=node:node_auto_index(name='comp') 
MATCH n<-[:Members_In]-x 
RETURN count(x.name), x.Sex

take a look at http://architects.dzone.com/articles/neo4jcypher-sql-style-group should be quite helpfull, thought my answer is kind of late...