Using count() and WITH

[jacob_vonderwel]

I am trying to right a query to determine the percent of people who purchased a specific product and then also purchased another product.

The query is evaluating "total" to 1 every time which is why I am getting the results I am.

What I want to be happening is this.

I just hard coded the number in. I just need help finding a way around this so I can get what is happening in the second picture without having to hard code the "total" in.

[ameyasoft]

Assuming count(purch1) = 4952 (which is 'total') ,
(count(purch2) / total) should give you the percentage.

[jacob_vonderwel]

Yes, that is what I try to do in the first query stated. However, for some reason "total" is being evaluated as 1 when I want it to be 4952 which messes up my percentages calculations.

[jacob_vonderwel]

I am unsure of why "total" is being evaluated to 1.

[ameyasoft]

For total, try count(distinct c1) as total

[Benoit_d · Accepted solution]

As you are holding the customers ("with ..., c1"), you will have one line for each customer. If we concider that all customer only buy one p1, your "count(p1) as total" will be always 1, but in many rows.

Try first following query:

match (c1:Customer)-[purch1:PURCHASED]-(p1:Product)
where p1.item_id = "81316" 
with count(purch1) as total, c1
return total 

then try this without c1:

match (c1:Customer)-[purch1:PURCHASED]-(p1:Product)
where p1.item_id = "81316" 
with count(purch1) as total
return total 

therefore you should avoid to overtake c1.
Following query is the same (it's just a different way to say products that are not "p1" are all "p2"), but the need of reuse c1 is avoided:

match (p1:Product)-[purch1:PURCHASED]-(c1:Customer)-[purch2:PURCHASED]-(p2:Product) 
where p1.item_id = "81316" 
return p1.item_id, count(purch1), p2.item_id, count(purch2), count(purch2)/count(purch1) as percents 
order by percents desc limit 10