Counting two node types in one query

[DC1]

I'm doing a count of two node types, and unless I use distinct on each I seem to get some type of product of the two. could someone please explain how the count works?

MATCH (n:post), (t:topic) 
RETURN 
count(distinct n) as c, 
count(distinct t) as ct
c	ct
113	82

without distinct I get

and 9266 = 113 x 82

[dominicvivek06 · Accepted solution]

So many ways to get count of nodes with labels, a few of them i use often are below ...

JSON Output

CALL apoc.meta.stats() YIELD labels
RETURN labels

as a collection

MATCH (n) 
RETURN DISTINCT count(labels(n)), labels(n);

Using db.labels()

CALL db.labels() YIELD label
CALL apoc.cypher.run('MATCH (:`'+label+'`) RETURN count(*) as count',{}) YIELD value
RETURN label, value.count

[DC1]

these are nice and great tips on using cypher, thanks!

but I'm still not clear on the reason why there is a distinct required. I mean I can see the query is doing some kind of set union, but it seems an odd interpretation of the cypher I sent... ?

[dana_canzano]

MATCH (n:post), (t:topic)  .....

is a cartesian join. No different than if in SQL RDBMs I had run

select count(post),cound(topic)
from Post as post, Topic as topic;

to perform 2 counts you could

match (n:post) with count(n) as count_post 
match (n:topic) with count(n) as count_topic,count_post
return count_post, count_topic;

For example and with Neo4j 4.2.1

@community> foreach (x in range (1,10000) | create (n:post {id:x}));
0 rows available after 968 ms, consumed after another 0 ms
Added 10000 nodes, Set 10000 properties, Added 10000 labels
@community> foreach (x in range (1,4870) | create (n:topic {id:x}));
0 rows available after 267 ms, consumed after another 0 ms
Added 4870 nodes, Set 4870 properties, Added 4870 labels
@community> match (n:post) with count(n) as count_post
            match (n:topic) with count(n) as count_topic,count_post
            return count_post, count_topic;
+--------------------------+
| count_post | count_topic |
+--------------------------+
| 10000      | 4870        |
+--------------------------+

[dominicvivek06]

Long story short - Cartesian Product. Scroll to end for Profiler

A small demo -

match (e:employee) return e;

╒═══════════════════════════════╕
│"e"                            │
╞═══════════════════════════════╡
│{"name":"Dominic","emp_id":"1"}│
├───────────────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │
├───────────────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │
├───────────────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │
├───────────────────────────────┤
│{"name":"Test1","emp_id":"5"}  │
├───────────────────────────────┤
│{"name":"Test2","emp_id":"6"}  │
├───────────────────────────────┤
│{"name":"Test3","emp_id":"7"}  │
├───────────────────────────────┤
│{"name":"Test4","emp_id":"8"}  │
├───────────────────────────────┤
│{"name":"Test5","emp_id":"9"}  │
└───────────────────────────────┘

match (d:department) return d;
╒═══════════════════════╕
│"d"                    │
╞═══════════════════════╡
│{"dept_name":"IT"}     │
├───────────────────────┤
│{"dept_name":"Finance"}│
├───────────────────────┤
│{"dept_name":"HR"}     │
├───────────────────────┤
│{"dept_name":"Admin"}  │
└───────────────────────┘

Its performing a cartesian join for the below query - 9 Employees * 4 Dept = 36

MATCH (n:employee), (t:department) 
RETURN n,t;

╒═══════════════════════════════╤═══════════════════════╕
│"n"                            │"t"                    │
╞═══════════════════════════════╪═══════════════════════╡
│{"name":"Dominic","emp_id":"1"}│{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Dominic","emp_id":"1"}│{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Dominic","emp_id":"1"}│{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Dominic","emp_id":"1"}│{"dept_name":"Admin"}  │
├───────────────────────────────┼───────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │{"dept_name":"Admin"}  │
├───────────────────────────────┼───────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │{"dept_name":"Admin"}  │
├───────────────────────────────┼───────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │{"dept_name":"Admin"}  │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test1","emp_id":"5"}  │{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test1","emp_id":"5"}  │{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Test1","emp_id":"5"}  │{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test1","emp_id":"5"}  │{"dept_name":"Admin"}  │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test2","emp_id":"6"}  │{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test2","emp_id":"6"}  │{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Test2","emp_id":"6"}  │{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test2","emp_id":"6"}  │{"dept_name":"Admin"}  │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test3","emp_id":"7"}  │{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test3","emp_id":"7"}  │{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Test3","emp_id":"7"}  │{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test3","emp_id":"7"}  │{"dept_name":"Admin"}  │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test4","emp_id":"8"}  │{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test4","emp_id":"8"}  │{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Test4","emp_id":"8"}  │{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test4","emp_id":"8"}  │{"dept_name":"Admin"}  │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test5","emp_id":"9"}  │{"dept_name":"IT"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test5","emp_id":"9"}  │{"dept_name":"Finance"}│
├───────────────────────────────┼───────────────────────┤
│{"name":"Test5","emp_id":"9"}  │{"dept_name":"HR"}     │
├───────────────────────────────┼───────────────────────┤
│{"name":"Test5","emp_id":"9"}  │{"dept_name":"Admin"}  │
└───────────────────────────────┴───────────────────────┘

Lets do for 1 label without Distinct

MATCH (n:employee), (t:department) 
RETURN (n)

╒═══════════════════════════════╕
│"n"                            │
╞═══════════════════════════════╡
│{"name":"Dominic","emp_id":"1"}│
├───────────────────────────────┤
│{"name":"Dominic","emp_id":"1"}│
├───────────────────────────────┤
│{"name":"Dominic","emp_id":"1"}│
├───────────────────────────────┤
│{"name":"Dominic","emp_id":"1"}│
├───────────────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │
├───────────────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │
├───────────────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │
├───────────────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │
├───────────────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │
├───────────────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │
├───────────────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │
├───────────────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │
├───────────────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │
├───────────────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │
├───────────────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │
├───────────────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │
├───────────────────────────────┤
│{"name":"Test1","emp_id":"5"}  │
├───────────────────────────────┤
│{"name":"Test1","emp_id":"5"}  │
├───────────────────────────────┤
│{"name":"Test1","emp_id":"5"}  │
├───────────────────────────────┤
│{"name":"Test1","emp_id":"5"}  │
├───────────────────────────────┤
│{"name":"Test2","emp_id":"6"}  │
├───────────────────────────────┤
│{"name":"Test2","emp_id":"6"}  │
├───────────────────────────────┤
│{"name":"Test2","emp_id":"6"}  │
├───────────────────────────────┤
│{"name":"Test2","emp_id":"6"}  │
├───────────────────────────────┤
│{"name":"Test3","emp_id":"7"}  │
├───────────────────────────────┤
│{"name":"Test3","emp_id":"7"}  │
├───────────────────────────────┤
│{"name":"Test3","emp_id":"7"}  │
├───────────────────────────────┤
│{"name":"Test3","emp_id":"7"}  │
├───────────────────────────────┤
│{"name":"Test4","emp_id":"8"}  │
├───────────────────────────────┤
│{"name":"Test4","emp_id":"8"}  │
├───────────────────────────────┤
│{"name":"Test4","emp_id":"8"}  │
├───────────────────────────────┤
│{"name":"Test4","emp_id":"8"}  │
├───────────────────────────────┤
│{"name":"Test5","emp_id":"9"}  │
├───────────────────────────────┤
│{"name":"Test5","emp_id":"9"}  │
├───────────────────────────────┤
│{"name":"Test5","emp_id":"9"}  │
├───────────────────────────────┤
│{"name":"Test5","emp_id":"9"}  │
└───────────────────────────────┘

With Distinct

MATCH (n:employee), (t:department) 
RETURN Distinct (n)
╒═══════════════════════════════╕
│"n"                            │
╞═══════════════════════════════╡
│{"name":"Dominic","emp_id":"1"}│
├───────────────────────────────┤
│{"name":"Vivek","emp_id":"2"}  │
├───────────────────────────────┤
│{"name":"Ravi","emp_id":"3"}   │
├───────────────────────────────┤
│{"name":"Rajesh","emp_id":"4"} │
├───────────────────────────────┤
│{"name":"Test1","emp_id":"5"}  │
├───────────────────────────────┤
│{"name":"Test2","emp_id":"6"}  │
├───────────────────────────────┤
│{"name":"Test3","emp_id":"7"}  │
├───────────────────────────────┤
│{"name":"Test4","emp_id":"8"}  │
├───────────────────────────────┤
│{"name":"Test5","emp_id":"9"}  │
└───────────────────────────────┘

Profiler is YOUR FRIEND ----

dominickumar.com

SQL Join – Dominic KUMAR's blog

w3resource

SQL cross join - w3resource

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table.