Returning two aggregates in a single Cypher query?

Question

I've been struggling some with Cypher in regards to taking the SUM of two values and finding the difference. I have these two queries, which find the total sent and the total received of a node:

START addr = node(5)
MATCH addr <- [:owns] - owner - [to:transfers] -> receiver
RETURN SUM(to.value) AS Total_Sent

START addr = node(5)
MATCH addr <- [:owns] - owner <- [from:transfers] - sender
RETURN SUM(from.value) AS Total_Received

Basically my question is - how do I combine these two separate queries so I can take the difference between Total_Sent and Total_Received? I have tried multiple start points like so:

START sendAddr = node(5), receivedAddr = node(5)
MATCH sendAddr <- [:owns] - sendOwner - [to:transfers] -> receiver, receivedAddr <- [:owns] - receiveOwner <- [from:transfers] - sender
RETURN SUM(to.value) AS Total_Sent, SUM(from.value) AS Total_Received, SUM(to.value) - SUM(from.value) AS Balance

But the Total_Received is null! To me this looks like a pretty simple use case - what the heck am I doing wrong?

Answer 1

You can't combine two queries by just smashing them together like that. :)

To solve this, I suggest you use WITH to separate your query into two parts, like this:

START addr = node(5)
MATCH addr <- [:owns] - owner - [to:transfers] -> receiver
WITH addr, SUM(to.value) AS Total_Sent

MATCH addr <- [:owns] - owner <- [from:transfers] - sender
WITH SUM(from.value) AS Total_Received, Total_Sent

RETURN Total_Received, Total_Sent, Total_Received - Total_Sent as Total_Balance

HTH,

Andrés