Working with aggregations

In Cypher, aggregations are supported using the COUNT, SUM, AVG, MIN, MAX, COLLECT, PERCENTILE, and STDEV functions. Except for the COLLECT function, all the other functions are standard mathematical functions. COLLECT functions create a list of entities similar to data pivoting by converting a list of rows into a column value.

We have seen the usage of the COUNT function numerous times in this chapter. We can combine COUNT and COLLECT to count the entities as well as collect the values as a list. Let us take a look at the drug prescription query where we were returning patient counts. We will also return the first names of those patients along with the count.

For this, the Cypher query looks like this:

MATCH (d:Drug)<-[:HAS_DRUG]-()<-[:HAS_ENCOUNTER]-(p)
WITH DISTINCT d, p
WITH d.description as drug,
     COUNT(p) as patients,
     COLLECT(p.firstName) as firstNames
WHERE patients > 10
RETURN...