jexp/cypher-avg-value-max-date.md

## cypher-avg-value-max-date.md

      
    Raw
  

              cypher-avg-value-max-date.md
            
          
    Select Average Value of Records with a Max Date in Cypher

Question: Oddman

Hmmmm. How would I go about getting the average of a value for a given date, for all records?
Ie. For all records on a given date, I want the highest value on that date, and then average
that value across all the results.
in SQL, it'd be something like avg... select max... group by date
Answer: Michael

if you have indexes on date and value this one should be quite efficient, see PROFILE
MATCH (r:Record) WITH max(r.date) as max
MATCH (r:Record) WHERE r.date = max
RETURN avg(r.value)
or to avoid the double read but use some more memory
MATCH (r:Record) WITH r.date as date, collect(r) as records ORDER BY date DESC LIMIT 1
UNWIND records as r
RETURN avg(r.value)
or this but it might be more expensive due to the early reads
MATCH (r:Record) 
RETURN r.date, apoc.coll.avg(collect(r.value)) as avg ORDER BY r.date DESC LIMIT 1