Question 136927 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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);
Tags:

8

Answers


  1. 0

    You can use DISTINCT to get only unique values, the query would look like this:

    SELECT * FROM cypher('test', $$
    MATCH (n:Person)
    WITH DISTINCT n.age AS age
    RETURN avg(age)
$$) as (avg agtype);
    Login or Signup to reply.
  2. 0

    Use the DISTINCT keyword inside the aggregate function

    SELECT *
FROM cypher('test', $$
MATCH (n:Person)
RETURN avg(DISTINCT n.age)
$$) as (avg_age agtype);
    Login or Signup to reply.
  3. 0

    The best way to do this is by "DISTINCT" clause(as answered above/below). One other way to achieve the same is by sub query.

    Here’s a way to do it in SQL:

    SELECT AVG(age) AS average FROM (SELECT DISTINCT age FROM "yourtable") AS unique;

    Change accordingly for cypher queries

    Login or Signup to reply.
  4. 0

    You can achieve this functionality using the DISTINCT keyword inside the aggregate function. DISTINCT eliminates all duplicate rows and keeps just one entry for each duplicated row group.

    You can use DISTINCT in two ways:

    In Aggregate Function:

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

    Using WITH clause:

    SELECT * FROM cypher('test', $$
    MATCH (n:Person)
    WITH DISTINCT n.age AS age
    RETURN avg(age)
$$) as (avg agtype);
    Login or Signup to reply.
  5. 0

    You can filter the value of ages using the DISTINCT keyword, and you can return the unique values of all ages in the :Person label using AGE by modifying your code:

    By including the keyword DISTINCT in your AVG function

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

    Or by using the WITH and DISTINCT keyword in your cypher query

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

    Hope this helps.

    Login or Signup to reply.
  6. 0

    You can update your query like

    SELECT * FROM cypher('graph_name_here', $$ 
 MATCH (n:Person)
RETURN avg(distinct n.age) 
$$) as (avg agtype)

    By doing Distinct you can get you desired Output

    Login or Signup to reply.
  7. 0

    Try with "WITH AND DISTINCT"

    SELECT * FROM cypher('test', $$ MATCH (n:Person) WITH DISTINCT n.age AS age RETURN avg(age) AS averageAge $$) AS AVG_AGE;
    Login or Signup to reply.
  8. 0

    In the query you wrote avg function will return the average of all items in the list even if there are duplicates they will be considered while computing the average So, the output of the query you wrote will be as follow :

    avg = (20+30+50+20+20+20 ) / 6 = 26.67

    If you want to only consider unique values in the list than you should use DISTINCT keyword to eliminate duplicates as follows :

    SELECT * FROM ag_catalog.cypher('graph_name', $$
 MATCH (n:Person)
 WITH DISTINCT n.age AS unique_age
 RETURN avg(unique_age)
 $$)
AS (result agtype);

    Note that WITH DISTINCT n.age AS unique_age will filter out duplicated items.
    So, now the output of the query will be as follows:

    avg = (20+30+50) / 3 = 33.3

    Only unique items will be used to compute the average of the list.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search