Cypher Query for find node count based on two property value

[janakishyama]

Hello,

I am trying to create a count table of products based on sales status. Sale status has two value - WON and LOST.

MATCH (sid:Sale)-[:have_outcome]->(ss:SaleStatus {name:'WON'})
MATCH (sid:Sale)-[:sales_of]->(p:Product)
WITH p.name as product, count(sid) as positive
MATCH (sid2:Sale)-[:have_outcome]->(ss2:SaleStatus {name:'LOST'})
MATCH (sid2:Sale)-[:sales_of]->(p2:Product)
RETURN product, positive, count(sid2) as negative

In the output I am not getting right value for 'negative' column

I would like to know how to solve it

Any small help or hint is appreciated and will surely help me a lot.

Thanks in advance.

[intouch_vivek]

janakishyama:

MATCH (sid:Sale)-[:have_outcome]->(ss:SaleStatus {name:'WON'})
MATCH (sid:Sale)-[:sales_of]->(p:Product)
WITH p.name as product, count(sid) as positive
MATCH (sid2:Sale)-[:have_outcome]->(ss2:SaleStatus {name:'LOST'})
MATCH (sid2:Sale)-[:sales_of]->(p2:Product)
RETURN product, positive, count(sid2) as negative

Hi @janakishyama,

Welcome to the community!!

Please tell us about the model and sample dataset.

[janakishyama]

Hello Vivek,

thanks for your reply.

The example dataset :

The Graph model is as follows:

I am able to fetch the product wise count in two separate queries, but unable to combine and create a single table in the output.

Thanks in advance.

[intouch_vivek · Accepted solution]

janakishyama:

MATCH (sid:Sale)-[:have_outcome]->(ss:SaleStatus {name:'WON'})
MATCH (sid:Sale)-[:sales_of]->(p:Product)
WITH p.name as product, count(sid) as positive
MATCH (sid2:Sale)-[:have_outcome]->(ss2:SaleStatus {name:'LOST'})
MATCH (sid2:Sale)-[:sales_of]->(p2:Product)
RETURN product, positive, count(sid2) as negative

Try below,

MATCH (p:Product) <-[:sales_of]- (sid:Sale)-[:have_outcome]->(ss:SaleStatus )
With p.name as product, ss.name as type, count(ss) as type_val
With product, collect([type,type_val]) as keypairlist 
With product, apoc.map.fromPairs(keypairlist)as value 
Return product, COALESCE(value.Won,0) as Won, COALESCE(value.Lost,0) as Lost

[janakishyama]

Hello Vivek,

Thanks for the reply.
Appreciate your time.

I run the query you suggested, unfortunately its showing a error. I tried my part but cant find a solution i am adding error message below. Please suggest a solution if possible.

Unknown function 'apoc.map.fromPairs' (line 4, column 15 (offset: 210))
"With product, apoc.map.fromPairs(keypairlist) as value"

Thanks in advance

[Cobra]

Have you installed APOC plugin on your database?

[janakishyama]

Hello Maxime,

Thanks for your help.

[intouch_vivek]

As suggested by Maxime install APOC

[janakishyama]

Hello Vivek,

I installed the APOC plugin now its working perfectly.
You provided a perfect code, even replaces the null value.
Thank you so much for your effort and help.