My neo4j relationships look something like....
(city:City {name:'city_name'})
-[:Has_Club]->
(c:Club {name:"ABC"})
-[:Has_Member]->
(p:Person{
name:"XYZ",
joinDate:'date_ in_datetime_format'
})
So for one particular city, I want to produce a record of member_count(cumulative) for every club related to city. I am trying to produce a record something like...
/ day clubA clubB clubC
1. date1 2 3 3
2. date2 3 5 3
3. date3 4 7 6
I googled something and implemented this query mentioned below
match
(city:City{name:"city_name"})
-[:Has_Club]->
(club:Club)
-[:Has_Member]->
(p:Person)
with p.joinDate as jd, club
return date(jd) as day, count(*), club.name order by day
But with this query, I am getting record which has column name as 'day', 'count' and 'clubName'.
/ day count clubName
1 date1 12 clubA
2 date2 31 clubB
3 date3 9 clubA
What do I need to change to see 'day' and club_Names as columns and make member_count add up in successive rows for that particular club.
no the columns i want are 'day', 'clubA' , 'clubB' , clubC and more club columns if they exist
/ day clubA clubB clubC
1. date1 2 3 3
2. date2 3 5 3
3. date3 4 7 6
So if you look at clubA column, 2 members have joinDate as date1 so total members which belong to club A at date1 are 2, then on date2 one more member got added to clubA , so on date2 total number of members that belong to clubA are 3 (i.e. 2+1), same goes for date3 and clubB. But if you look at clubC on date1 only 3 members got added but on date2 there was no addition in clubC so on date2 the members' count doesn't increase and remains 3 on date2. On date3 3 members were added to clubC so total members belonging to clubC by the end of date3 was 6(i.e. 3+3).
Not sure what you are asking for about the columns. Are the three columns, 'day', 'count', and 'clubName' ok? Are you looking for a running total per club over time, such as
/ day total clubName
1 date1 12 clubA
2 date2 31 clubB
3 date3 21 clubA
@glilienfield Any help would be highly appreciated
Here is the output for my test data:
Try this. It works for a fixed know set of club names. You need to substitute with your specific names, where I have 'A', 'B', and 'C'.
match (club:Club)-[:Has_Member]->(p:Person)
with p.joinDate as jd, club.name as club, count(p) as dailyClubCount
with collect(distinct jd) as dates, collect({date: jd, club: club, count: dailyClubCount}) as clubStats
order by dates
unwind dates as date
return date,
reduce(s=0,i in [x in clubStats where x.club='A' and x.date<=date|x.count] | s + i) as `A`,
reduce(s=0,i in [x in clubStats where x.club='B' and x.date<=date|x.count] | s + i) as `B`,
reduce(s=0,i in [x in clubStats where x.club='C' and x.date<=date|x.count] | s + i) as `C`