title: NEO4J Cypher Query: Multiple Aggregates
body: <p>Sample Data</p>
  
  <p><a href="https://i.stack.imgur.com/N6cHe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/N6cHe.png" alt="enter image description here"></a></p>
  
  <blockquote>
    <p>Sample Query: At the end of this post</p>
  </blockquote>
  
  <p><strong>Objective: Searches similar to 'who knows exactly 2 Cust and works at 1 company'</strong></p>
  
  <hr>
  
  <p><strong>Step 1</strong>: I just did a print of the number of connected Cust and Comp and its all good till now</p>
  
  <pre><code>MATCH (from:Cust), (a:Cust), (b:Comp),
  p1=((from)-[r1]-(a)), p2=((from)-[r2]-(b))
  WITH from, count(DISTINCT a) as knows, count(DISTINCT b) as works 
  RETURN from.title, knows, works
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/9Piyv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9Piyv.png" alt="enter image description here"></a></p>
  
  <hr>
  
  <p><strong>Step 2</strong>: I went and added WHERE clause to filter the count, so far so good</p>
  
  <pre><code>MATCH (from:Cust), (a:Cust), (b:Comp),
  p1=((from)-[r1]-(a)), p2=((from)-[r2]-(b))
  WITH from, count(DISTINCT a) as knows, count(DISTINCT b) as works 
  WHERE knows=2 and works=1
  RETURN from.title, knows, works
  </code></pre>
  
  <p>Result > Alpha | 2 | 1 </p>
  
  <hr>
  
  <p><strong>Step 3</strong>: Now I also want some filters on Cust and Comp add a and b to my WITH clause</p>
  
  <pre><code>MATCH (from:Cust), (a:Cust), (b:Comp),
  p1=((from)-[r1]-(a)), p2=((from)-[r2]-(b))
  WITH from, count(DISTINCT a) as knows, count(DISTINCT b) as works, a, b
  RETURN from.title, knows, works
  </code></pre>
  
  <p>And BOOM! everything is 1, 1 </p>
  
  <p><a href="https://i.stack.imgur.com/X51eH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/X51eH.png" alt="enter image description here"></a></p>
  
  <p>It looks aggregate is getting confused with multiple variables in the WITH clause so and start to add multiple WITH clause and UNWIND, but I was unable to get the query for it.</p>
  
  <p><strong>Sample Data Creation</strong></p>
  
  <pre><code>CREATE (a:Cust {title: "Alpha"})
  CREATE (b:Cust {title: "Bravo"})
  CREATE (c:Cust {title: "Charlie"})
  CREATE (d:Cust {title: "Delta"})
  
  create (g:Comp {title: "Google"})
  create (f:Comp {title: "Facebook"})
  create (s:Comp {ttile: "Stackoverflow"})
  
  MATCH (a:Cust {title: "Alpha"}), (b:Cust {title: "Bravo"})
  CREATE (a)-[:KNOWS]-&gt;(b)
  
  MATCH (a:Cust {title: "Alpha"}), (c:Cust {title: "Charlie"})
  CREATE (a)-[:KNOWS]-&gt;(c)
  
  MATCH (d:Cust {title: "Delta"}), (c:Cust {title: "Charlie"})
  CREATE (d)-[:KNOWS]-&gt;(c)
  
  MATCH (c:Cust {title: "Charlie"}), (b:Cust {title: "Bravo"})
  CREATE (c)-[:KNOWS]-&gt;(b)
  
  
  MATCH (g:Comp {title: "Google"}), (s:Comp {ttile: "Stackoverflow"})
  CREATE (g)-[:USES]-&gt;(s)
  
  MATCH (f:Comp {title: "Facebook"}), (s:Comp {ttile: "Stackoverflow"})
  CREATE (f)-[:USES]-&gt;(s)
  
  MATCH (d:Cust {title: "Delta"}), (s:Comp {ttile: "Stackoverflow"})
  CREATE (d)-[:WORKS_AT]-&gt;(s)
  
  MATCH (d:Cust {title: "Delta"}), (g:Comp {title: "Google"})
  CREATE (d)-[:WORKS_AT]-&gt;(g)
  
  MATCH (a:Cust {title: "Alpha"}), (f:Comp {title: "Facebook"})
  CREATE (a)-[:WORKS_AT]-&gt;(f)
  
  MATCH (c:Cust {title: "Charlie"}), (s:Comp {ttile: "Stackoverflow"})
  CREATE (c)-[:WORKS_AT]-&gt;(s)
  </code></pre>