Neo4j Aggregate Multiple Lines into a Map

Question

I have the following Cypher script:

MATCH (sy:SchoolYear)<-[:TERM_OF*]-()<-[:DAY_OF]-(d:Day)
WHERE sy.year = 2015
OPTIONAL MATCH (d)<-[:START]-(e:Enrollment)-[:AT]->(s:School)
RETURN d.date, s.abbreviation, count(e)
ORDER BY d.date

This gives me all of the dates in the range that I want and returns number of students that have enrolled for each school for that date, or null. The only issue I have is that different schools are on different lines, causing a single date to have multiple lines. I would like to aggregate those into a single line per date.

I'm given:

1/1/2000, School 1, 5
1/1/2000, School 2, 10
1/2/2000, null, null
1/3/2000, School 1, 6

What I would like:

1/1/2000, {School 1 : 5, School 2: 10}
1/2/2000, null
1/3/2000, {School 1: 6}

I've tried:

MATCH (sy:SchoolYear)<-[TERM_OF*]-()<-[:DAY_OF]-(d:Day)
WHERE sy.year = 2015
OPTIONAL MATCH (d)<-[:START]-(e:Enrollment)-[:AT]->(s:School)
WITH d, s.abbreviation as abb, count(e) as enr
RETURN d.date, {abb:enr}
ORDER BY d.date

How should I go about this?

Answer 1

Here is how I would go with this aggregate each school into a map and the maps into a collection

MATCH (sy:SchoolYear)<-[TERM_OF*]-()<-[:DAY_OF]-(d:Day)
WHERE sy.year = 2015
OPTIONAL MATCH (d)<-[:START]-(e:Enrollment)-[:AT]->(s:School)
WITH  d, s, count(e) as students
RETURN d.date, collect({name:s.abbreviation, students:students})
ORDER BY d.date

Answer 2

This is a bit ugly, but I think it returns what you are after. I tried using the school name as a key just like you did in your example and I could not get that to work either. In the end I resorted to this.

MATCH (sy:SchoolYear)<-[TERM_OF*]-()<-[:DAY_OF]-(d:Day)
WHERE sy.year = 2015
OPTIONAL MATCH (d)<-[:START]-(e:Enrolment)-[:AT]->(s:School)
// collect the schools and their counts together
with d, [s.abbreviation, count(e)] as school_count
// collect all of the school counts together by date
with d.date as date, collect(school_count) as school_counts
// format the school counts as a string with the schools
// as keys and the counts as values
with  date, reduce( out = "", s in school_counts | out + s[0] + " : " + s[1] + ", " ) as school_count_str
return  date, '{ ' + left(school_count_str, length(school_count_str)-2) + ' }' as school_counts
order by date