CODEWITHSUNDEEP
neo4jcypher
Matt
Matt

Reputation: 468

Count nodes with a certain property

I'm working on a dataset describing legislative co-sponsorship. I'm trying to return a table with the name of the bill, the number of legislators who co-sponsored it and then the number of co-sponsors who are Republican and the number who are Democrat. I feel like this should be simple to do but I keep getting syntax errors. Here's what I have so far: 

 MATCH (b:Bill{Year:"2016"})-[r:COAUTHORED_BY|COSPONSORED_BY|SPONSORED_BY]-(c:Legislators) 
WHERE b.name CONTAINS "HB" OR b.name CONTAINS "SB"
RETURN b.name, b.Short_description, COUNT(r) AS TOTAL, COUNT(c.Party = "Republican"), COUNT(c.Party = "Democratic")
ORDER BY COUNT(r) desc

However, in the table this query produces the count of Republican and Democrat sponsors and the count of total sponsors, are all the same. Obviously, the sum of number of Rep and Dem sponsors should equal the total.

What is the correct syntax for this query?

Upvotes: 2

Views: 2218

Answers (3)

stdob--
stdob--

Reputation: 29172

Use the filter:

MATCH  (b:Bill{Year:"2016"})
      -[r:COAUTHORED_BY|COSPONSORED_BY|SPONSORED_BY]-
       (c:Legislators) 
WHERE b.name CONTAINS "HB" OR b.name CONTAINS "SB"
WITH b, collect(distinct c) as Legislators
RETURN b.name, 
       b.Short_description, 
       SIZE(Legislators) AS TOTAL, 
       SIZE(FILTER(c in Legislators WHERE c.Party = "Republican")) as Republican,
       SIZE(FILTER(c in Legislators WHERE c.Party = "Democratic")) as Democratic
ORDER BY TOTAL desc

Upvotes: 2

InverseFalcon
InverseFalcon

Reputation: 30397

Assuming that legislators can ONLY be Republican or Democratic (we'll need to make some adjustments if this isn't the case):

MATCH (b:Bill{Year:"2016"})
WHERE b.name CONTAINS "HB" OR b.name CONTAINS "SB"
WITH b
OPTIONAL MATCH (b)-[:COAUTHORED_BY|COSPONSORED_BY|SPONSORED_BY]-(rep:Legislators)
WHERE rep.Party = "Republican"
OPTIONAL MATCH (b)-[:COAUTHORED_BY|COSPONSORED_BY|SPONSORED_BY]-(dem:Legislators)
WHERE dem.Party = "Democratic"
WITH b, COUNT(DISTINCT rep) as reps, COUNT(DISTINCT dem) as dems
RETURN b.name, b.Short_description, reps + dems AS TOTAL, reps, dems
ORDER BY TOTAL desc

Upvotes: 1

Supamiu
Supamiu

Reputation: 8731

This is a graph model problem, you shouldn't be counting nodes by their properties, if some nodes can have the same property and you want to count in this property, you need to create an intermediate node to set the party:

(b:Bill)-[:SPONSORED_AUTHORED]->(i:Intermediate)-[:TARGET]->(c:Legislators)

and then you create a relation between your intermediate node and the party:

(i:Intermediate)-[:BELONGS_PARTY]->(p:Party{name:"Republican"})

The intermediate node represents the data you actually have in your relationship, but it allows you to create relationships between your operation and a party, making counting easier and way faster.

Keep in mind that this is just an example, without knowing the context I don't know what should be the Intermediate real label and its property, it's just a demo of the concept.

I answered a question using this, feel free to check it (it's a real life example, maybe easier to understand): Neo4j can I make relations between relations?

Upvotes: 0

Related Questions