I have the following query:
START e1=node:event(prop="0")
MATCH e1-[r:rbel]->e2
WITH e1, e2, count(e1) as ecount
MATCH e1-[:redge]->p<-[:redge]-e2
WITH p.element_type as Type, p.label as Label, (count(p)*100./ecount) as percentage
WHERE percentage > 20
RETURN Type, Label, ROUND(percentage) as Percentage
I am trying to calculate the percentage of times the specified pattern occurs in events with prop="0", over all patterns occurring in those events.
I receive the following error: Unknown identifier 'ecount'
So I replaced ecount in the calculation with count(ecount), and that consistently yielded percentages of 100%, which I know not to be true.
Am I going about this wrong? How can I carry the value of ecount to the WITH clause and use it in calculation?
Any help is appreciated!
Does this query work for you? Whenever I combine e1 and count(e1) in a WITH statement, the count(e1) is always 1. I think that is because the count(e1) aggregation does not work any more when you select e1, too. Either you leave out the e1 or the count(e1).
START e1=node:event(prop="0")
MATCH e1-[r:rbel]->e2
WITH e1, e2
MATCH e1-[:redge]->p<-[:redge]-e2
WITH p.element_type as Type, p.label as Label, (count(p)*100./count(e1)) as percentage
WHERE percentage > 20
RETURN Type, Label, ROUND(percentage) as Percentage
UPDATE After playing around with your provided console setup I got the following query working:
START e1=node:node_auto_index(prop="0")
MATCH e1-[r:rbel]->e2
WITH COLLECT(e2) AS e2collection, count(e1) AS cnt
MATCH e1-[:redge]->p<-[:redge]-(e2)
WITH p, COLLECT(e1) AS e1collection, cnt, e2collection
WITH p.name AS Name, cnt, count(p)*100/cnt AS Percentage
WHERE Percentage > 20
RETURN Name, Percentage