How to GROUP_BY and take average using Cypher queries in Neo4j

Question

I have 2 nodes created in graph database, airport and carrier. It is related by a property named 'delayed_by'.

    MATCH (origin:origin_airport {name: row.ORIGIN}), 
   (carrier:Carrier {name: row.UNIQUE_CARRIER})
   CREATE (origin)-[:delayed_by {dep_delay: row.avg_dep_delay}]->(carrier)
   ")

Here, origin_airport has name of the origin airport and Carrier has the name of the carriers. dep_delay holds the value of which each flight is delayed. Here I need to group by carrier and calculate the average value of delay for each carrier using cypher query in Neo4j. Representing in tabular format, my input file will of the format as described below:

   ORIGIN   UNIQUE_CARRIER  avg_dep_delay
     ABE      DL             15
     ABE      EV             12
     ABQ      DL             14
     ABQ      AS             2
     ABQ      EV             20
     ABQ      AS             6

I want the result in the below format. I need to group by UNIQUE_CARRIER and get the average value of departure delay for each of the carrier.

    UNIQUE_CARRIER  avg_dep_delay

         DL           14.5
         EV           16
         AS           4

I am not sure about the query to group_by and take average value for each of the group. I am getting a syntax error while using the below query.

MATCH (oa:origin_airport)-[d:delayed_by]->(c:Carrier)
WITH c, AVG(d) As avg
RETURN c.name  AS Carrier, avg
ORDER BY avg DESC
LIMIT 10

stdob-- stdob-- · Accepted Answer · 2016-06-17T22:54:03

You need specify property to AVG:

MATCH ()-[d:delayed_by]->(c:Carrier)
RETURN c.name  AS Carrier, 
       AVG(toFloat(d.dep_delay)) As avg
    ORDER BY avg DESC
    LIMIT 10

How to GROUP_BY and take average using Cypher queries in Neo4j

1 Answers