How to count an attribute based on its value

[paulhighams]

I'm trying to count an attribute based on its value.

I have a node "athlete" that has a relationship "attends" to a node "competition", on the relationship "attends" is an attribute "FinishPosition" which is a number.

<(ath:Athlete)-[att:ATTENDS]->(cmp:Competition)/>

How do i count for each athlete how many times they finished first, second and third to create a medal table

Athlete First Second third
fred 3 1 0
Joe 2 4 2
etc.

Ive been trying count(att.FinishPosition=1) etc. and CASE statements but can't get the right result.

I'm sure the answer is simple and I'm missing a trick.

Thanks
Paul

[david_allen]

Match all of the athlete's "attends" relationships, then gather them up with collect.

Use cypher pattern comprehensions to pull out which ones were 1st, 2nd, and 3rd. What you end up
with is a filtered list of all of the relationships that are just 1st place finishes.

Then just count the size of the list.

MATCH (ath:Athlete)-[att:ATTENDS]->(cmp:Competition)
WITH ath, collect(att) as atts
RETURN ath.name, 
   size([r IN atts WHERE r.FinishPosition = 1]) as firstPlaceFinishes,
   size([r IN atts WHERE r.FinishPosition = 2]) as secondPlaceFinishes,
   size([r IN atts WHERE r.FinishPosition = 2]) as thirdPlaceFinishes;