Neo4j Cypher query that joins and groups results

0
votes

I am trying to take this MySQL query and write it in Neo4j's Cypher language.

MySQL Query

SELECT COUNT(o.id) as orders, AVG(o.total) as avg FROM Order AS o
  INNER JOIN Product AS p ON o.id = p.orderId
WHERE p.productType = 'donation'
GROUP BY o.id

Cypher Query (so far)

MATCH (o:Order)-[:CONTAINS]->(p:Product)
WHERE p.productType CONTAINS 'donation'
RETURN COUNT(o) AS orders, AVG(o.orderBaseGrandTotal) AS avg;

My problem is that the match is returning duplicate Order results, I believe because if an order has multiple donation products then multiple nodes are returned for the same order.

Essentially I'm trying to get all orders that have at least one donation product. Then, return how many orders have a donation product in the order and what the average order value is for those orders.

Anyone have advice on this query? Thanks in advance!

1
joinneo4jcypherdistinctaverage

1 Answers

2
votes

You're almost there, we just have to get distinct orders before returning.

MATCH (o:Order)-[:CONTAINS]->(p:Product)
WHERE p.productType CONTAINS 'donation'
WITH DISTINCT o
RETURN COUNT(o) AS orders, AVG(o.orderBaseGrandTotal) AS avg;