Getting a total, maximum and ratio in query

Neo4j Graph Platform Cypher
, , ,
8 
MATCH (n:Allele)

# HERE is your trouble. Let me explain...
# WITH n, max(n.hetCount + n.homCount as totCountMax
WITH max(n.hetCount + n.homCount as totCountMax

MATCH (n:Allele)
WITH n, totCountMax, n.hetCount + n.homCount AS totCount
WHERE totCount > 0
RETURN n, n.hetCount, n.homCount, totCount, totCountMax as outOf, totCount / totCountMax AS ratio
ORDER BY totCount ASC

If you think about Cypher MATCH results as a spreadsheet (you should check the sheet results in these cases), you'll get one row for every MATCH. Aggregate functions will have an inherent GROUP BY on any variables you're including.

I've built a little NeoConsole to play with: Neo4j Console
image

x.val = 1
y.val = 2
z.val = 4

Now, what happens below, is that Cypher is trying to keep the b contents in the result, so the SUM aggregate function won't collapse the values of l.val beyond that. Essentially turning whatever you KEEP IN CONTEXT (b here) as an implicit GROUP BY.

MATCH (b:Box)-[]-(l:Label)
RETURN b, SUM(l.val) as sum;

image

However, if you omit that variable to get an aggregate of all the things...

MATCH (b:Box)-[]-(l:Label)
RETURN SUM(l.val) as sum;

image

You get the aggregate you need, the trick is then in using it across all the b nodes here...

MATCH (b:Box)-[]-(l:Label)
WITH SUM(l.val) as sum
MATCH (b:Box)-[]-(l:Label)
# now you have SUM and b, and l in context...
RETURN b, l, sum

image

1 Like