get runing total after getting node count using count(*)

[amritanshu1912]

I am trying to fetch date-wise running total of member counts.
The relationships in data base are like this 

(city:City) //has name property
-[:Has_Club]->
(c:Club) // has name property
-[:Has_Member]->
(p:Person{
    name:"XYZ", //string
    joinDate: date}) // in_datetime_format

So if  the member count is as shown below I want to generate the running total 

/    day     mem_count on a particular date           running total for memcount
1.   date1         30                                             30
2.   date2         10                                             40 // 10 + 30
3.   date3         15                                             55 // 15 + 40 

I wrote this query to use REDUCE function in neo4j. I only wanted the day column and running_total_column

match  
  (city:City{name:"city_name"})
   -[:Has_Club]->
  (club:Club{name:"clubName"})
   -[:Has_Member]->
  (p:Person)
        
  with p.joinDate as jd, collect(count(*)) as dateColl
  return distinct(date(jd)) as day, reduce(cons = 0, i in dateColl | i+ cons) as memberCount

But it gave the following error

Can't use aggregate functions inside of aggregate functions. (line 13, column 36 (offset: 401))
"    with p.joinDate as jd, collect(count(*)) as dateColl"

What  am I doing wrong? What do  I need to  change in my Query?

[Cobra]

Hello @amritanshu1912 🙂

This query should do what you want:

MATCH (city:City {name: "city_name"})-[:Has_Club]->(club:Club {name: "clubName"})-[:Has_Member]->(p:Person) 
RETURN date(p.joinDate) AS jd, count(DISTINCT p) AS memberCount

Regards,
Cobra

[amritanshu1912]

i want to get the running total, this query you have suggested only produces member count of a particular day. in your suggested query, is there any way I can collect the memberCount values in a list, so that I can use that list with reduce function in the return statement

[Cobra]

The result should be?

• first column : the date
• second column : the number of person for the date
• third column : the weighted sum from the first date

[amritanshu1912]

@Cobra only the dates and weighted sum (i.e. accumulated sum or running total)

[Cobra]

Can you share some Cypher queries to create a little dataset to work with please? It will be helpful to develop the query.

[glilienfield]

Try this:

match (club:Club)-[:Has_Member]->(p:Person)
with p.joinDate as jd, count(p) as dailyClubCount
with collect(jd) as dates, collect({date: jd, count: dailyClubCount}) as dailyStats
order by dates
unwind dates as date
return date,
    reduce(s=0,i in [x in dailyStats where x.date<=date|x.count] | s + i) as `Running Count`