Search code examples
postgresqlapache-age

How can I return the average of all unique values of ages with an OpenCypher query in Apache AGE

I'm dealing with a project where I want to return the average value of all unique ages, for example users` ages are [20,30,50,20,20,20], the average of unique ages will be (20+30+50)/3.

I used avg() function, but it doesn't solve the problem, it returns the average of all values including duplicates.

SELECT *
FROM cypher('test', $$
MATCH (n:Person)
RETURN avg(n.age)
$$) as (avg agtype);
Solution

  • Use the DISTINCT keyword inside the aggregate function

    SELECT *
FROM cypher('test', $$
MATCH (n:Person)
RETURN avg(DISTINCT n.age)
$$) as (avg_age agtype);