Group by date and sum some results

[surfooo]

Hello,

I have this query:

MATCH(g:Geocache)-[l:LOG]->(:Log) WHERE g.referenceCode = 'GC728BR' AND l.typeId = 2 RETURN date(l.loggedDate) AS date, l.favoritePoint AS fp ORDER BY l.loggedDate

and the result is like this:

╒════════════╤═════╕
│"date"      │"fp" │
╞════════════╪═════╡
│"2018-02-09"│true │
├────────────┼─────┤
│"2018-02-09"│true │
├────────────┼─────┤
│"2018-02-09"│true │
├────────────┼─────┤
│"2018-02-11"│true │
├────────────┼─────┤
│"2018-02-11"│true │
├────────────┼─────┤
│"2018-02-11"│false│
├────────────┼─────┤
│"2018-02-11"│true │
├────────────┼─────┤
│"2018-02-11"│false│
├────────────┼─────┤
│"2018-02-12"│true │
├────────────┼─────┤
│"2018-02-12"│true │
├────────────┼─────┤

I'd like to aggregate the result by date, the count of the same date, and the count of "FP" at the value true.
Example:

╒════════════╤═══════╤═════╕
│"date"      │"found"│"fp" │
╞════════════╪═══════╪═════╡
│"2018-02-09"│3      │3    │
├────────────┼───────┼─────┤
│"2018-02-11"│5      │3    │
├────────────┼───────┼─────┤
│"2018-02-12"│2      │2    │
├────────────┼───────┼─────┤

I have tried several solutions, but I can't get the right result...
Can you help me please?

Thank you!

[ameyasoft]

Try adding GROUP BY clause:

MATCH(g:Geocache)-[l:LOG]->(:Log) WHERE g.referenceCode = 'GC728BR' AND l.typeId = 2
RETURN date(l.loggedDate) AS date, l.favoritePoint AS fp
GROUP BY date, fp ORDER BY l.loggedDate

[andrew_bowman · Accepted solution]

Cypher doesn't have a GROUP BY clause, the grouping key consists of the variables present at the time of the aggregation.

As a result this is a tricky aggregation, as you want to group the occurrence across just one variable (the date), but have the count from two variables (date and the boolean value).

One way you can do this is to aggregate the count by date and collect the fp values at the same time, giving you your count for the date, then get the size of the filtered list by keeping only true values:

MATCH(g:Geocache)-[l:LOG]->(:Log) 
WHERE g.referenceCode = 'GC728BR' AND l.typeId = 2 
WITH date(l.loggedDate) AS date, count(l) as found, collect(l.favoritePoint) AS fps
RETURN date, found, size([fp in fps WHERE fp = true]) as fp

You can of course just use the single collect() aggregation and use the size to get the counts you want as well:

MATCH(g:Geocache)-[l:LOG]->(:Log) 
WHERE g.referenceCode = 'GC728BR' AND l.typeId = 2 
WITH date(l.loggedDate) AS date, collect(l.favoritePoint) AS fps
RETURN date, size(fps) as found, size([fp in fps WHERE fp = true]) as fp

[surfooo]

Oh thank you Andrew, it's excellent!

[surfooo]

Oh I'm using neo4j-bolt-php driver and I got this error:
Date is not supported as a return type in Bolt protocol version 1. Please make sure driver supports at least protocol version 2. Driver upgrade is most likely required.

I guess there's nothing I can do about it....

[andrew_bowman]

As noted you're using an older version of the driver. This error is only about returning a result that is a date type, so to get around this you can convert this to a string representation. Use toString(date) as date in the RETURN, that should work.