Return Min, Max of Aggregate functions

[pruthviraj_v]

Hi All,

I have a product database where all the products are associated with Category and Sub Category relations.

The matching cypher looks like this

MATCH (C:Category)-[:hasChild]->(S:SubCategory)-[:hasChild]-(SC:SubCategoryr2)-[:hasProduct]->(P:Product) 

I am getting the count of products under each Category and Sub Category combination using the following Cypher

MATCH (C:Category)-[:hasChild]->(S:SubCategory)-[:hasChild]-(SC:SubCategoryr2)-[:hasProduct]->(P:Product)  RETURN C.Name,S.Name,SC.Name,Count(P) as ProductCount

The above command gives the output like below.

But I am looking for Max and Min of the ProductCount and in each row print the difference between ProcutCount and Max and Min of ProductCount.

I had written the following cypher but it does not work

MATCH (C:Category)-[:hasChild]->(S:SubCategory)-[:hasChild]-(SC:SubCategoryr2)-[:hasProduct]->(P:Product)  RETURN C.Name,S.Name,SC.Name,Count(P) as ProductCount, Max(ProductCount) as MaxProdCount , Min(ProductCount) as MinProdCount, MaxProdCount - ProductCount as MaxProdDiff

The output that I am expecting is as shown below But the above command is not successful.Could you please suggest how this can be achieved.

neo4j version : 4.13, desktop version : Windows 10

[Cobra · Accepted solution]

Hello @pruthviraj_v

You can use APOC plugin to achieve this:

MATCH (C:Category)-[:hasChild]->(S:SubCategory)-[:hasChild]-(SC:SubCategoryr2)-[:hasProduct]->(P:Product)
WITH C.Name AS c_name, S.Name AS s_name, SC.Name AS sc_name, count(P) AS ProductCount
WITH, c_name, s_name, sc_name, ProductCount, collect(ProductCount) AS counts
RETURN c_name, s_name, sc_name, ProductCount, apoc.coll.max(counts) AS MaxProdCount, apoc.coll.min(counts) AS MinProdCount, apoc.coll.max(counts) - ProductCount AS MaxProdDiff

Regards,
Cobra

[pruthviraj_v]

Thanks so much This was really helpful