DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Fixing Common Oracle Database Problems
  • How to Restore a Transaction Log Backup in SQL Server
  • Why I Built the Ultimate Text Comparison Tool (And Why You Should Try It)
  • Enhancing Avro With Semantic Metadata Using Logical Types

Trending

  • Google Cloud Document AI Basics
  • Unlocking AI Coding Assistants Part 3: Generating Diagrams, Open API Specs, And Test Data
  • Unlocking the Benefits of a Private API in AWS API Gateway
  • Unlocking AI Coding Assistants Part 2: Generating Code
  1. DZone
  2. Data Engineering
  3. Databases
  4. Neo4j/Cypher: Combining COUNT and COLLECT in One Query

Neo4j/Cypher: Combining COUNT and COLLECT in One Query

By 
Mark Needham user avatar
Mark Needham
·
Feb. 27, 13 · Interview
Likes (0)
Comment
Save
Tweet
Share
10.8K Views

Join the DZone community and get the full member experience.

Join For Free

Curator's Note: Check raw code snippets to see properly formatted tables.

In my continued playing around with football data I wanted to write a cypher query against neo4j which would show me which teams had missed the most penalties this season and who missed them.

I started off with a query that returned all the penalties that have been missed this season and the games those missed happened in:

START player = node:players('name:*')
MATCH player-[:missed_penalty_in]-game, 
      player-[:played|subbed_on]-stats-[:in]-game,
      stats-[:for]-team,
      game-[:home_team]-home,
      game-[:away_team]-away
RETURN player.name, team.name, home.name, away.name

+-------------------------------------------------------------------------------------------------+
| player.name          | team.name              | home.name              | away.name              |
+-------------------------------------------------------------------------------------------------+
| "Papiss Demba Cisse" | "Newcastle United"     | "Newcastle United"     | "Norwich City"         |
| "Wayne Rooney"       | "Manchester United"    | "Manchester United"    | "Arsenal"              |
| "Mikel Arteta"       | "Arsenal"              | "Arsenal"              | "Fulham"               |
| "David Silva"        | "Manchester City"      | "Manchester City"      | "Southampton"          |
| "Frank Lampard"      | "Chelsea"              | "Manchester City"      | "Chelsea"              |
| "Adel Taarabt"       | "Queens Park Rangers"  | "Queens Park Rangers"  | "Norwich City"         |
| "Javier Hernández"   | "Manchester United"    | "Manchester United"    | "Wigan Athletic"       |
| "Robin Van Persie"   | "Manchester United"    | "Southampton"          | "Manchester United"    |
| "Jonathan Walters"   | "Stoke City"           | "Fulham"               | "Stoke City"           |
| "Shane Long"         | "West Bromwich Albion" | "West Bromwich Albion" | "Liverpool"            |
| "Steven Gerrard"     | "Liverpool"            | "Liverpool"            | "West Bromwich Albion" |
| "Lucas Piazon"       | "Chelsea"              | "Chelsea"              | "Aston Villa"          |
+-------------------------------------------------------------------------------------------------+
12 rows

(there should actually be another penalty miss for Jonathan Walters against Chelsea but for some reason the data source has missed it off!

I then grouped the penalty misses by team so that I’d have one row for each team and a collection showing the people who’d missed.

We can use the COLLECT function to do the latter:

START player = node:players('name:*')
MATCH player-[:missed_penalty_in]-game, 
      player-[:played|subbed_on]-stats-[:in]-game,
      stats-[:for]-team
RETURN DISTINCT team.name, COLLECT(player.name) AS players

I wanted to order the teams by the number of penalties they’d missed so Manchester United would be first in the table in this case and initially tried to order the results by a count of players:
START player = node:players('name:*')
MATCH player-[:missed_penalty_in]-game, 
      player-[:played|subbed_on]-stats-[:in]-game,
      stats-[:for]-team
RETURN DISTINCT team.name, COLLECT(player.name) AS players
ORDER BY COUNT(player.name)

which doesn’t actually compile:

SyntaxException: Aggregation expressions must be listed in the RETURN clause to be used in ORDER BY

I tried a few other variations such as the following:

START player = node:players('name:*')
MATCH player-[:missed_penalty_in]-game, 
      player-[:played|subbed_on]-stats-[:in]-game,
      stats-[:for]-team
RETURN DISTINCT team.name, COUNT(player.name) AS numberOfPlayers, 
       COLLECT(player.name) AS players
ORDER BY numberOfPlayers DESC

which again doesn’t compile:

SyntaxException: Aggregation expressions must be listed in the RETURN clause to be used in ORDER BY

I eventually found a post by Andres where he explains that you need to split the query into two and make use of WITH if you want to make use of two aggregation expressions.

I ended up with the following query which does the job:

START player = node:players('name:*')
MATCH player-[:missed_penalty_in]-game, 
      player-[:played|subbed_on]-stats-[:in]-game,
      stats-[:for]-team
WITH DISTINCT team, COLLECT(player.name) AS players
 
MATCH player-[:missed_penalty_in]-game, 
      player-[:played|subbed_on]-stats-[:in]-game,
      stats-[:for]-team
WITH DISTINCT team, COUNT(player) AS numberOfPlayers, players
 
RETURN team.name, players
ORDER BY numberOfPlayers DESC
+---------------------------------------------------------------------------------+
| team.name              | players                                                |
+---------------------------------------------------------------------------------+
| "Manchester United"    | ["Wayne Rooney","Javier Hernández","Robin Van Persie"] |
| "Chelsea"              | ["Frank Lampard","Lucas Piazon"]                       |
| "Liverpool"            | ["Steven Gerrard"]                                     |
| "Manchester City"      | ["David Silva"]                                        |
| "Newcastle United"     | ["Papiss Demba Cisse"]                                 |
| "Queens Park Rangers"  | ["Adel Taarabt"]                                       |
| "Stoke City"           | ["Jonathan Walters"]                                   |
| "Arsenal"              | ["Mikel Arteta"]                                       |
| "West Bromwich Albion" | ["Shane Long"]                                         |
+---------------------------------------------------------------------------------+
9 rows




Database

Published at DZone with permission of Mark Needham, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Fixing Common Oracle Database Problems
  • How to Restore a Transaction Log Backup in SQL Server
  • Why I Built the Ultimate Text Comparison Tool (And Why You Should Try It)
  • Enhancing Avro With Semantic Metadata Using Logical Types

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!