Display all combinations for a table with 3 columns by using sum aggregation

cypher
(Gabriela) #1

I am stuck with trying to find a solution to my problem.
Here's my code :

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

What I actually want to do is to display the SommeUnits for all ReordLevel and the SommeUnits for every ReordLevel (example : the sum of all Units for ReordLevel 25, then for 30, etc), but my code it isn't complete and i don't know how to figure it out.

0 Likes

(Michael Hunger) #2

Hi,

Why would you call your report summary also Product? that totally messes up the data model.
Not sure why you want to do a MERGE aka database update/insert you just want to return a summary data?

It's probably easiest to just do a UNION.

MATCH(p:Product)
RETURN sum(p.unitsInStock) as SommeUnits, p.supplierID as Supplier, p.reorderLevel as ReordLevel
ORDER BY ReordLevel
UNION ALL
MATCH(p:Product)
RETURN sum(p.unitsInStock) as SommeUnits, p.supplierID as Supplier, 'Total' as ReordLevel
ORDER BY ReordLevel

otherwise you could do this:

MATCH(p:Product)
WITH sum(p.unitsInStock) as SommeUnits, p.supplierID as Supplier, p.reorderLevel as ReordLevel
WITH Supplier, sum(SommeUnits) as Total, collect([ReordLevel, SommeUnits]) as entries
UNWIND entries as entry
RETURN Supplier, Total, entry[0] as ReordLevel, entry[1] as SommeUnits
ORDER BY ReordLevel
0 Likes