[FIXED] How to get a simple return without same output double (for a Cypher query)?

Issue

I currently get this as an undesired result from my query:

I am looking for a query that delivers this result:

I use this query, which delivers me the undesired result:

MATCH q = (r:Representative {name: "Graaf"})-[job:FACILITATED]->(b)-[x: jaar_sinds_registratie_coördinator]->(a)
WHERE a.year < 13 AND NOT b:VuurpijlMatch
WITH DISTINCT count(labels(b)) AS countlabel1, labels(b) as labelname1
WITH DISTINCT labelname1, countlabel1

MATCH q = (r:Representative {name: "Graaf"})-[job:FACILITATED]->(b)-[x: jaar_sinds_registratie_coördinator]->(a)
WHERE a.year > 24 AND NOT b:VuurpijlMatch
WITH DISTINCT count(labels(b)) AS countlabel2, labels(b) as labelname2, countlabel1
RETURN DISTINCT labelname2, countlabel2, countlabel1

Can anyone provide me an answer / show me what I overlook in this case?

Thanks!

Solution

The problem lies in this aggregation operation WITH DISTINCT count(labels(b)) AS countlabel2, labels(b) as labelname2, countlabel1. When you do this countlabel2 is calculated for each distinct combination of labelname2 and countlabel1. That's why you get duplicate records. For reference, your countlabel2 is calculated for the following combinations:

labelname2    countlabel1

NetworkMatch  136
NetworkMatch  5
SettledMatch  136
SettledMatch  5

This can be fixed by also fetching labelname1 in the WITH so that your combinations for aggregations become this:

labelname2    labelname1    countlabel1

NetworkMatch  NetworkMatch  136
NetworkMatch  SettledMatch  5
SettledMatch  NetworkMatch  136
SettledMatch  SettledMatch  5

After this, we can simply return the rows, where labelname1 is equal to labelname2. So your query now becomes something like this:

MATCH q = (r:Representative {name: "Graaf"})-[job:FACILITATED]->(b)-[x: jaar_sinds_registratie_coördinator]->(a)
WHERE a.year < 13 AND NOT b:VuurpijlMatch
WITH DISTINCT count(labels(b)) AS countlabel1, labels(b) as labelname1
WITH DISTINCT labelname1, countlabel1

MATCH q = (r:Representative {name: "Graaf"})-[job:FACILITATED]->(b)-[x: jaar_sinds_registratie_coördinator]->(a)
WHERE a.year > 24 AND NOT b:VuurpijlMatch
WITH DISTINCT count(labels(b)) AS countlabel2, labels(b) as labelname2, countlabel1, labelname1
WITH labelname2, countlabel2, countlabel1 WHERE labelname2 = labelname1
RETURN labelname2, countlabel2, countlabel1

Answered By - Charchit Kapoor

Answer Checked By - Terry (PHPFixing Volunteer)