Create a node and assign it the sum of a column of the table in Cypher Neo4j

Question

I am trying to figure out how to implement the OLAP operation Roll-up in Cypher. I am stuck with finding how to create a node and then assign it the sum of a column of a table in Cypher. More particularly, I am trying to achieve this result : Code result [EDITED] With this code I am obtaining the result as seen in the picture :

MATCH(p:Product)
WITH sum(p.unitsInStock) as SommeUnits, p.supplierID as Supplier, p.reorderLevel as ReordLevel
CREATE(reord:Product {productName : "TotalReord", unitsInStock : SommeUnits})
RETURN ReordLevel,Supplier, SommeUnits
ORDER BY ReordLevel

Relationships : Supplier-[:SUPPLIES]->(Product) Attributs of Product : unitsInStock, productName, productId, unitsInOrder, supplierID

The problem is that this code is not working properly because it generates not just one node but multiples, and the result that I get is obtained only if I play the query twice.

Answer 1

Aggregating functions like SUM use the non-aggregated items in the same (WITH or RETURN) clause as "grouping keys". So, your WITH clause is generating a SommeUnits value for each distinct SommeUnits/ReordLevel combination. And your CREATE is being called for each SommeUnits value.

This query should work properly (assuming that you want every returned record to have the same SommeUnits value):

MATCH(p:Product) WHERE p.productName <> "TotalReord"
WITH SUM(p.unitsInStock) AS SommeUnits, COLLECT(p) AS ps
MERGE(t:Product {productName: "TotalReord"})
SET t.unitsInStock = SommeUnits
WITH ps, SommeUnits
UNWIND ps AS p
RETURN p.reorderLevel AS ReordLevel, p.supplierID AS Supplier, SommeUnits
ORDER BY ReordLevel

This query uses MERGE to avoid creating duplicate "TotalReord" nodes every time you call this query. And the WHERE clause filters out the "TotalReord" node, so that its existing unitsInStock value will not be used when calculating the new sum, inflating it.

If you actually want each returned record to have the sum just for that record's SommeUnits/ReordLevel combination, you can do this:

MATCH(p:Product) WHERE p.productName <> "TotalReord"
WITH SUM(p.unitsInStock) AS u, p.reorderLevel AS r, p.supplierID AS s
WITH COLLECT({u:u, r:r, s:s}) AS data, SUM(u) AS total
MERGE(t:Product {productName: "TotalReord"})
SET t.unitsInStock = total
WITH data
UNWIND data AS d
RETURN d.r AS ReordLevel, d.s AS Supplier, d.u AS SommeUnits
ORDER BY ReordLevel

[UPDATED]

Finally, if you want to do the previous query but also get the overall total, this query will show the overall total as the fourth value in each returned record:

MATCH(p:Product) WHERE p.productName <> "TotalReord"
WITH SUM(p.unitsInStock) AS u, p.reorderLevel AS r, p.supplierID AS s
WITH COLLECT({u:u, r:r, s:s}) AS data, SUM(u) AS total
MERGE(t:Product {productName: "TotalReord"})
SET t.unitsInStock = total
WITH data, total
UNWIND data AS d
RETURN d.r AS ReordLevel, d.s AS Supplier, d.u AS SommeUnits, total
ORDER BY ReordLevel