Count nodes having different values against same key as different variable names in a single iteration

Question

I am working on something with neo4j. My database has a label called 'Request' which has one particular property, the key is 'status' and the value can be 'APPROVED', 'PENDING' or 'REJECTED'. Now I want to count all the request nodes as totalNodes, nodes that have request.status = "APPROVED" as approvedRequests, nodes which have request.status = "PENDING" as pendingRequests, and same with request.status = "REJECTED". I had done one implementation earlier like this

match
    (shop:Shop)
    -[:HAS_request]->
    (request:Request)

with count(distinct request) as totalrequests,
sum(case when request.status = "APPROVED" then 1 else 0 end) as appovedrequests,
sum(case when request.status = "PENDING" then 1 else 0 end) as pendingRequests,
sum(case when request.status = "REJECTED" then 1 else 0 end) as rejectedRequests

return totalrequests, approvedRequests, pendingRequests, rejectedRequests

but I think it will use multiple passes for all the request nodes. I want to declare these 3 variables (approvedReq, pendingReq, rejectedReq) and start iterating the request label nodes and when request.status = 'approved' , approvedReq should increase by 1, request.status = 'pending', pendingReq should increase by 1, and the same goes for 'rejected' case. This way I don't have to iterate all the nodes 3 times. Is there any way I can do it in a single pass?

Answer 1

You can try this, using a combination of APOC functions and reduce:

MATCH (shop:Shop)-[:HAS_request]->(request:Request)
WITH COLLECT(DISTINCT r) AS requests
WITH size(requests) AS totalRequests, 
reduce(accumulator = {approvedRequests: 0, rejectedRequests: 0, pendingRequests: 0}, 
req IN requests | CASE WHEN req.status = 'APPROVED' THEN apoc.map.setEntry(accumulator, 'approvedRequests', accumulator.approvedRequests + 1)
WHEN req.status = 'PENDING' THEN apoc.map.setEntry(accumulator, 'pendingRequests', accumulator.pendingRequests + 1)
WHEN req.status = 'REJECTED' THEN apoc.map.setEntry(accumulator, 'rejectedRequests', accumulator.rejectedRequests + 1) ELSE accumulator END) AS results
RETURN totalRequests, results.approvedRequests AS approvedRequests, results.pendingRequests AS pendingRequests, results.rejectedRequests AS rejectedRequests