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);
8
Answers
You can use DISTINCT to get only unique values, the query would look like this:
Use the DISTINCT keyword inside the aggregate function
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:
Change accordingly for cypher queries
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:
Using WITH clause:
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 yourAVG
functionOr by using the
WITH
andDISTINCT
keyword in your cypher queryHope this helps.
You can update your query like
By doing Distinct you can get you desired Output
Try with "WITH AND DISTINCT"
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 :If you want to only consider unique values in the list than you should use
DISTINCT
keyword to eliminate duplicates as follows :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:
Only unique items will be used to compute the average of the list.