CODEWITHSUNDEEP
neo4jcypher
Arturs
Arturs

Reputation: 111

Conditional sum in Neo4j Cypher query

I have a Neo4j database of bank accounts and their incoming transfers. Each transfer occurs at some hour. There are 24 hourNodes representing each different hour (1,2,...,24). I need to know the total money transferred to each account for each hour, like this (yes, I really need 25 columns in the resulting table!):

accountName     totalH1   totalH2   ...   totalH24
My Account #1   19        50        ...   34
My Account #2   5         14        ...   99
...

This simple query:

MATCH (account)->(transfer)->(hourNode)
RETURN
  account.name,
  hourNode.hour,
  sum(transfer.amount)

gives results in a different format:

accountName     hour   total
My Account #1   1      19
My Account #1   2      50
...
My Account #1   24     34
My Account #2   1      5
...

I cannot change database structure. How would I write a query to return the data in the format described?

Upvotes: 1

Views: 659

Answers (1)

Stefan Armbruster
Stefan Armbruster

Reputation: 39905

Don't know a way how to exactly create the output you've described, but we can easily get very close to that.

What you basically want is a row per name and a ordered list of sums. You can do that using a WITH that cares about ordering and a collect to build the list:

MATCH (account)->(transfer)->(hourNode)
WITH
  account.name as name,
  hourNode.hour as hour,
  sum(transfer.amount) as sum
ORDER BY name, hour
RETURN name, collect(hour) as hours, collect(sum) as sums

Upvotes: 1

Related Questions