---
title: Filtered Aggregates
url: https://nexyron.tech/docs/cypher/functions/aggregation/filtered-aggregates
description: Use filtered aggregates when you want an aggregate to include only rows that satisfy an explicit condition without rewriting the whole query into manual CASE expressions yourself
---

# Filtered Aggregates

## Generic Description

Use filtered aggregates when you want an aggregate to include only rows that satisfy an explicit condition without rewriting the whole query into manual `CASE` expressions yourself.

Simple example:

```cypher
MATCH (u:User)-[:HAS_APP_SESSION]->(s:AppSession)
RETURN count_if(s.mode = 'focused') AS focused_sessions,
       sum_if(s.duration_seconds, s.mode = 'focused') AS focused_duration
```

## Consumer-Level Explanation

Filtered aggregates let you ask “aggregate this value, but only when a condition is true” in one expression.

The currently implemented forms are:

-   `count_if`
-   `sum_if`
-   `avg_if`
-   `min_if`
-   `max_if`

This is useful because a lot of product analytics and operational reporting is conditional by nature:

-   active users only
-   premium cohort only
-   sessions above a threshold only
-   events inside a mode or state only

The point is not just convenience. It keeps the intent visible in the query instead of burying it inside application code or hand-written `CASE` scaffolding.

## More Detailed Explanation

Nexyron lowers the filtered aggregate forms into standard aggregate semantics built from `CASE WHEN ... THEN ... ELSE NULL END`.

That means:

-   the engine does not need a second filtered-aggregate execution model
-   semantics stay aligned with the normal aggregate families
-   planner and executor reuse stays simple

Conceptually:

-   `count_if(predicate)` becomes a non-null count over a boolean-gated value
-   the `*_if(value, predicate)` forms become the corresponding aggregate over a value that becomes `NULL` when the predicate is false

This is the right tradeoff here because it keeps the implementation mathematically explicit and easy to reason about.

## Advanced Example

```cypher
MATCH (u:User)-[:HAS_APP_SESSION]->(s:AppSession)
TIME s.started_at BETWEEN datetime('2026-04-01T00:00:00Z') AND datetime('2026-04-30T23:59:59Z')
WITH u, s, toLower(s.mode) AS normalized_mode
RETURN u.profile.location.country AS country,
       count_if(normalized_mode = 'focused') AS focused_sessions,
       avg_if(s.duration_seconds, normalized_mode = 'focused') AS focused_avg_duration,
       max_if(s.duration_seconds, normalized_mode = 'deep') AS deepest_session,
       min_if(s.duration_seconds, s.duration_seconds >= 300) AS shortest_long_session
ORDER BY country
```

## Real Use Cases

-   active versus inactive cohort reporting in one grouped query
-   moderation or abuse analytics where only flagged rows should contribute to a metric
-   app-session summaries segmented by mode, quality, or device condition
-   operational dashboards where thresholded events need to be summarized without multiple passes

## Real Limitations And Tradeoffs

-   filtered aggregates are still only as good as the predicate you write; expensive predicates can make grouped analytics harder to read and more costly to execute
-   because the current runtime is Cypher-first, these forms are documented and optimized as part of the Cypher path only
-   for very large grouped sets, execution-path choice still depends on whether the planner can keep exact semantics on the spill-backed route