CODEWITHSUNDEEP
sqlneo4jdatasetcypheraggregate-functions
Divya Rustagi
Divya Rustagi

Reputation: 3

How do I group nodes by a different node property and order by a different one in Neo4j?

I am working with crimes in Boston dataset, with each crime as nodes in Neo4j. I want to query and display the most committed crimes each year to get a result like this:

Year offense_code_group count(offense_code_group)
2015 Aggravated Assault 5827
2016 Larceny From Motor Vehicle 11534
2017 Verbal Disputes 12049
2018 Investigate Person 8724

Note: The result is just an example of how I want it to group and look, not the actual result I'll get after querying the dataset.

But this is the best I have been able to do so far: query and output in neo4j desktop

I know there is no GROUP BY clause in Neo4j and I have tried using collect(), but I can't get it to work.

Upvotes: 0

Views: 34

Answers (1)

Luanne
Luanne

Reputation: 19373

How about something like this:

MATCH (c:Crime)
WITH c.year AS year, c.offense_code_group AS code, count(c.offense_code_group) AS count
ORDER BY year, count DESC
WITH year, COLLECT(code) AS codes, COLLECT(count) AS counts
RETURN year, codes[0], counts[0]

Upvotes: 1

Related Questions