cancel
Showing results for 
Search instead for 
Did you mean: 

Return Min, Max of Aggregate functions

pruthviraj_v
Node Link

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.

3X_a_5_a53680b05221f2a73eaec8228872ab8b27a44675.png

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.

3X_5_4_54aa2ce6432e9aeb40cce8acacef4ddd2ab97aa6.png

neo4j version : 4.13, desktop version : Windows 10

1 ACCEPTED SOLUTION

Cobra
Ninja
Ninja

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

View solution in original post

2 REPLIES 2

Cobra
Ninja
Ninja

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

Thanks so much This was really helpful