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

General
1

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

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?

2

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

3

Hello @amritanshu1912 :slightly_smiling_face:

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

4

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
5

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

6

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

7

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`