cancel
Showing results for 
Search instead for 
Did you mean: 

Head's Up! Site migration is underway. Phase 2: migrate recent content

How to fetch list items as column names and get cumulative count for every column

amritanshu1912
Node Link

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.

5 REPLIES 5

glilienfield
Ninja
Ninja

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

amritanshu1912
Node Link

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).

amritanshu1912
Node Link

@glilienfield Any help would be highly appreciated

glilienfield
Ninja
Ninja

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`

 

glilienfield
Ninja
Ninja

Here is the output for my test data:

Screen Shot 2022-11-10 at 3.43.08 PM.png

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

All the sessions of the conference are now available online