# How could to replace groupe by roll up and group by cube in cypher (aggregation)

**URL:** <https://community.neo4j.com/t/how-could-to-replace-groupe-by-roll-up-and-group-by-cube-in-cypher-aggregation/44762>\
**Category:** Cypher\
**Tags:** cypher\
**Created:** [September 22, 2021, 2:32pm UTC](https://community.neo4j.com/t/how-could-to-replace-groupe-by-roll-up-and-group-by-cube-in-cypher-aggregation/44762 "2021-09-22T14:32:21Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![redha\_benhisse1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/redha_benhisse1/32/4969_2.png) [@redha\_benhisse1](https://community.neo4j.com/u/redha_benhisse1)\
**Post date:** [September 22, 2021, 2:32pm UTC](https://community.neo4j.com/t/how-could-to-replace-groupe-by-roll-up-and-group-by-cube-in-cypher-aggregation/44762/1 "2021-09-22T14:32:21Z")

</div>

Hi evrybody

I built a datawarehouse in neo4j, but I have a probleme to remplace group by roll up or group by cube in cypher?

for exmple :

Q1 : SELECT ProductID, CustomerID, SUM(SalesAmount) FROM Sales GROUP  
BY ROLLUP(ProductID, CustomerID)

OR : SELECT ProductID, CustomerID, SUM(SalesAmount) FROM Sales GROUP  
BY CUBE(ProductID, CustomerID)

I m using northwind database from microsoft

anybody can help me about this? thanks by advance

---

<div class="post-metadata">

**Author:** ![michael.hunger](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/michael.hunger/32/27377_2.png) [@michael.hunger](https://community.neo4j.com/u/michael.hunger)\
**Post date:** [September 23, 2021, 8:36am UTC](https://community.neo4j.com/t/how-could-to-replace-groupe-by-roll-up-and-group-by-cube-in-cypher-aggregation/44762/2 "2021-09-23T08:36:59Z")

</div>

You should be able to use

```auto
MATCH (s:Sales)
WITH s.ProductID as pid, s.CustomerID as cid, sum(s.salesAmount) as sa
CALL {
   WITH pid, sum(sa) as saProduct RETURN saProduct 
} 
CALL {
   WITH cid, sum(sa) as saProduct RETURN saCustomer 
} 
RETURN pid, cid, saProduct, saCustomer, sa

```

You can also check out apoc.agg.stats  
and comment on this issue

> <https://github.com/neo4j-contrib/neo4j-apoc-procedures/issues/2182>
>
> For a property you want to have more than one statistic:
> 
> size, count, avg, me…dian, ...
> 
> which is probably already covered by \`apoc.agg.stats()\`
> 
> but then, how about multi-dimensional aggregation.
> 
> e.g.
> 
> \`apoc.agg.multiStats(\[key1,key2,key3\]) -\> Map\<Key,Map\<agg="sum,count,avg", number\>\>\`
> 
> e.g.
> 
> 
> \`\`\`
> match (p:Person)
> with apoc.agg.multiStats(p, \["wcc","lpa","louvain"\]) as data
> match (p:Person) 
> return p.name, data\[toString(p.wcc)\].count as size
> 
> \`\`\`
> 
> 
> 
> 
> see 
> https://community.neo4j.com/t/listing-the-community-size-of-different-community-detection-algorithms-already-calculated/42895/2?u=michael.hunger

---

<div class="post-metadata">

**Author:** ![redha\_benhisse1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/redha_benhisse1/32/4969_2.png) [@redha\_benhisse1](https://community.neo4j.com/u/redha_benhisse1)\
**Post date:** [September 24, 2021, 3:41pm UTC](https://community.neo4j.com/t/how-could-to-replace-groupe-by-roll-up-and-group-by-cube-in-cypher-aggregation/44762/3 "2021-09-24T15:41:32Z")

</div>

thanks for your answer

but the problem persist again for me

by the way , my graph scema is like that

 ![image](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/3X/2/e/2e23a8759518d375f3980e0a4ad56c8cb3381c98.png)

and when i use call command in my request, it does'nt get the same result.

but when i use union command, thats work

```auto
MATCH (c:customer)-[rel:buy]->(s:sales)<-[rel2:sold]-(p:product) 
MATCH (sm:sales_amount {sales_amount_id: s.sales_id})
WITH c.customer_id as cid,p.product_id as pid,sum(sm.sales_amount) as sum
RETURN cid, pid, sum
UNION
MATCH (c:customer)-[rel:buy]->(s:sales)<-[rel2:sold]-(p:product) 
MATCH (sm:sales_amount {sales_amount_id: s.sales_id})
WITH null as cid, null as pid, sum(sm.sales_amount) as sum
RETURN cid, pid, sum
UNION
MATCH (c:customer)-[rel:buy]->(s:sales)<-[rel2:sold]-(p:product) 
MATCH (sm:sales_amount {sales_amount_id: s.sales_id})
WITH c.customer_id as cid, null as pid, sum(sm.sales_amount) as sum
RETURN cid, pid, sum;

```

do you have any idea how can i use call and with to replace union im my requeste to optimize it?

than you so by advance

---

<div class="post-metadata">

**Author:** ![redha\_benhisse1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/redha_benhisse1/32/4969_2.png) [@redha\_benhisse1](https://community.neo4j.com/u/redha_benhisse1)\
**Post date:** [September 27, 2021, 8:28am UTC](https://community.neo4j.com/t/how-could-to-replace-groupe-by-roll-up-and-group-by-cube-in-cypher-aggregation/44762/4 "2021-09-27T08:28:42Z")

</div>

hi

of course, in my real data base implimented in neo4j, my nodes are labaled like that (comparativly with schema):

Acr\_pruduct = product  
Acr\_customer = customer  
Acr\_Fact\_Sales = sales  
H\_Att\_SalesAmount = sales\_amount

---

<div class="post-metadata">

**Author:** ![michael.hunger](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/michael.hunger/32/27377_2.png) [@michael.hunger](https://community.neo4j.com/u/michael.hunger)\
**Post date:** [September 30, 2021, 10:59am UTC](https://community.neo4j.com/t/how-could-to-replace-groupe-by-roll-up-and-group-by-cube-in-cypher-aggregation/44762/5 "2021-09-30T10:59:01Z")

</div>

Subqueries with CALL are available from 4.1, so best would be to upgrade anyhow to a recent version like 4.3