cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

amritanshu1912
Node Link

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?

6 REPLIES 6

Cobra
Ninja
Ninja

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
Node Link

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

 

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

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

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

glilienfield
Ninja
Ninja

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`
Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

All the sessions of the conference are now available online