CODEWITHSUNDEEP
neo4jcypher
degath
degath

Reputation: 1621

Cypher query with group by inside aggregation

I created a cypher query which returns list of people in country & some avg calculation:

Match (people:People)-[:LOCATED_IN]->(address:Address), (people)-[:SOME]->(calc)
RETURN address.country as name, count(*) as count, avg(calc.value) as value

Which returns for instance:

name, count, value
PL  , 5    , 10
UK  , 3    , 20
US  , 6    , 30
DE  , 3    , 40

What I'm trying to achieve right now is to group a few of them in some custom groups. For instance, if the country is PL & UK return it AS Custom:

name   , count, value
Custom , 8    , 15
US     , 6    , 30
DE     , 3    , 40

I was able to achieve it by creating two separate queries one where I specified: WHERE address.country IN ['PL','UK'] and second with negation, and I combined it after that. Is there any easier solution for that?

Upvotes: 0

Views: 507

Answers (2)

cybersam
cybersam

Reputation: 67044

You could use a custom name map and the COALESCE function. You could even pass the (possibly empty) map as a parameter (instead of defining it statically) if the custom name mapping can change.

WITH {PL: 'Custom', UK: 'Custom'} AS cMap
MATCH (calc)<-[:SOME]-(people:People)-[:LOCATED_IN]->(address:Address)
RETURN
  COALESCE(cMap[address.country], address.country) as name, 
  COUNT(*) AS count,
  AVG(calc.value) AS value

Upvotes: 1

Tomaž Bratanič
Tomaž Bratanič

Reputation: 6534

You can solve this with a CASE statement:

Match (people:People)-[:LOCATED_IN]->(address:Address), (people)-[:SOME]->(calc)
RETURN CASE WHEN address.country in ['PL','UK'] THEN 'Custom' ELSE address.country END as name, 
  count(*) as count, avg(calc.value) as value

Upvotes: 1

Related Questions