Counting specific values for individual years in a graph database

Question

I'm trying to count how many baptisms and marriages took place in a particular church in a particular year. Doing this is not so hard and this query allows me to do so (e.g. for the year 1702):

MATCH (e:baptism)-[:TOOK_PLACE_IN]->(ch:church:Church_of_Utrecht {Name: 'De Noteboom'})
WHERE 1701 < e.Event_year < 1703
RETURN count (e:baptism) AS Number_of_baptisms

However, when wanting to do this for a period of 50 years, it quickly becomes very time consuming. I would like to have a query like the above one but which does this calculation for each year in a given period (say from 1700 to 1750) and gives the output in two columns (year and number of baptisms). I reckon I'll need to use a FOR EACH statement, but I'm completely stuck. Any suggestions?

julielinx julielinx · Accepted Answer · 2019-12-17T15:50:47

Actually, it's easier than you think. You just need to throw the year into the return statement. Cypher automatically groups based on the return values (this is also a gotcha if you don't want this behavior, so beware of that).

So your query becomes:

MATCH (e:baptism)-[:TOOK_PLACE_IN]->(ch:church:Church_of_Utrecht {Name: 'De Noteboom'})
WHERE 1700 =< e.Event_year <= 1750
RETURN e.Event_year as Year, count(distinct e) AS Number_of_baptisms

You can read more about this behavior in the Aggregating Functions section of the Cypher Manual.

Counting specific values for individual years in a graph database

1 Answers