Neo4j cypher query for groupBy multiple entities

Question

Neo4j cypher query to get order counts:groupBy year and then month and then org wise

    MATCH (year)-[:MONTH]->(month:Month) 
    WITH year,month 
    MATCH (month)-[r:DAY]->(day:Day) 
    WITH year,month,day 
    OPTIONAL MATCH (order:Order)-[r:ORD_CREATED]->(day) 
    WITH year,month,order
    MATCH (order)-[:ORDER_OF]-(org)
    RETURN  year,month,COLLECT(DISTINCT(org.name),count(order))

how can i achieve the following output ? any help would be appreciated

 {
 year
 [{month1,[ {org1,20},{ org2,30}]}],
 [{month2,[ {org1,26},{ org2,10}]}],
}

Chris White Chris White · Accepted Answer · 2016-10-03T03:51:55

Perhaps not optimal, but works for me:

// match the path from an Org to the Day of order
MATCH (day:Day)<-[:ORD_CREATED]-(order:Order)-[:ORDER_OF]-(org:Org)
// count number of paths for each day & org 
WITH day, org, count(*) as opd
// match on the month for each day
MATCH (day)<-[:DAY]-(month:Month)
// sum the order counts for each month & org
WITH month, org.name as name, SUM(opd) as opm
// for each month, create a collection of org order counts
WITH month, COLLECT({ org:name, cnt:opm }) as mon_cnt
// match on the year for each month
MATCH (month)<-[:MONTH]-(year:Year)
// create the final structure of year, list of months, org order counts per month 
RETURN year.year, COLLECT({ month:month.text, mon_cnt:mon_cnt });

http://console.neo4j.org/r/y9qyzl for a sample graph and execution result

|year.year│COLLECT({ month:month.text, mon_cnt:mon_cnt })              │
╞═════════╪════════════════════════════════════════════════════════════╡
│2015     │[{month: Feb 2015, mon_cnt: [{org: Org5, cnt: 2}, {org: Org2│
│         │, cnt: 1}, {org: Org3, cnt: 1}, {org: Org4, cnt: 1}, {org: O│
│         │rg1, cnt: 1}]}, {month: Jan 2015, mon_cnt: [{org: Org3, cnt:│
│         │ 1}, {org: Org2, cnt: 1}, {org: Org4, cnt: 1}]}]            │
├─────────┼────────────────────────────────────────────────────────────┤
│2016     │[{month: Feb 2016, mon_cnt: [{org: Org2, cnt: 1}, {org: Org1│
│         │, cnt: 1}, {org: Org5, cnt: 1}]}]                           │

Neo4j cypher query for groupBy multiple entities

1 Answers