sum (windowcomp)

Use the `sum()` function within the `windowcomp` stage to compute the sum of a specified numeric field over a window of rows. Unlike the `comp` stage version, the `windowcomp` version preserves all original rows and adds the computed sum as a new field. This is equivalent to `SUM() OVER(...)` in SQL.

## Syntax

```sql
| windowcomp sum(<field>) [by <partition_field1>, <partition_field2>, ...] [sort [asc|desc] <sort_field1>, ...] [between <lower> [and <upper>] [frame_type=rows|range]] [as <alias>]
```

## Parameters

| Name | Type | Required | Description |
| ----------------- | ----------------- | -------- | --------------------------------------------------------------------------------------------------------------------- |
| `field` | numeric | Yes | The numeric field whose values will be summed over the window. |
| `partition_field` | any | No | One or more fields to partition the data by (equivalent to SQL `PARTITION BY`). |
| `sort_field` | any | No | One or more fields to define the order within each partition (equivalent to SQL `ORDER BY`). Defaults to ascending. |
| `lower` | integer or `null` | No | Lower bound of the window frame. `0` = current row, negative = rows before, `null` = unbounded. |
| `upper` | integer or `null` | No | Upper bound of the window frame. `0` = current row, positive = rows after, `null` = unbounded. |
| `frame_type` | `rows` or `range` | No | Type of window frame. `rows` (default) uses physical row offsets; `range` uses value-based offsets on the sort field. |
| `alias` | string | No | An alias for the output field. |

## Returns

**Type**: numeric

**Description**: The `sum()` function returns the sum of all non-NULL values within the defined window for each row. All original rows are preserved.

## Usage notes

* **Row preservation**: Unlike `comp sum()`, the `windowcomp sum()` does not reduce the number of rows. Each row retains its original data and gets an additional field with the window sum.
* **Running total**: When combined with `sort`, the default frame (`between null and 0`) produces a cumulative running total.
* **No partitioning**: If no `by` clause is specified, the window spans the entire result set.
* **Default frame**: If no window frame is specified, the default frame is from the start of the partition to the current row (`between null and 0`).
* **Null handling**: NULL values are ignored in the computation.

## Examples

### Example 1: Cumulative bytes sent over time

**Goal**: Compute a running total of bytes sent, ordered by time.

**XQL code**:

```sql
dataset = xdr_data
| windowcomp sum(bytes_sent) sort asc _time as cumulative_bytes
```

**Explanation**: By sorting on `_time` in ascending order, the `sum()` function computes a cumulative running total of `bytes_sent` from the start of the dataset up to the current row.

**Output**:

| \_TIME | BYTES\_SENT | CUMULATIVE\_BYTES |
| ------------------- | ----------- | ----------------- |
| 2024-01-15 08:00:00 | 100 | 100 |
| 2024-01-15 09:00:00 | 250 | 350 |
| 2024-01-15 10:00:00 | 150 | 500 |
| 2024-01-15 11:00:00 | 300 | 800 |

### Example 2: Total bytes per host (all rows preserved)

**Goal**: Compute the total bytes sent per host while preserving all original rows.

**XQL code**:

```sql
dataset = xdr_data
| windowcomp sum(bytes_sent) by agent_hostname as total_bytes_per_host
```

**Explanation**: The `sum()` function computes the total `bytes_sent` within each `agent_hostname` partition. All original rows are preserved, and each row receives the partition's total.

**Output**:

| \_TIME | AGENT\_HOSTNAME | BYTES\_SENT | TOTAL\_BYTES\_PER\_HOST |
| ------------------- | --------------- | ----------- | ----------------------- |
| 2024-01-15 08:00:00 | workstation-1 | 100 | 500 |
| 2024-01-15 09:00:00 | workstation-1 | 250 | 500 |
| 2024-01-15 10:00:00 | workstation-1 | 150 | 500 |
| 2024-01-15 08:30:00 | workstation-2 | 300 | 700 |
| 2024-01-15 09:30:00 | workstation-2 | 400 | 700 |

### Example 3: Moving sum within a sliding window

**Goal**: Compute the sum of bytes sent within a sliding window of 3 rows.

**XQL code**:

```sql
dataset = xdr_data
| windowcomp sum(bytes_sent) sort asc _time between -1 and 1 as moving_sum
```

**Explanation**: The `between -1 and 1` clause defines a sliding window of 3 rows centered on the current row. The `sum()` function returns the total `bytes_sent` within that window for each row.

**Output**:

| \_TIME | BYTES\_SENT | MOVING\_SUM |
| ------------------- | ----------- | ----------- |
| 2024-01-15 08:00:00 | 100 | 350 |
| 2024-01-15 09:00:00 | 250 | 500 |
| 2024-01-15 10:00:00 | 150 | 700 |
| 2024-01-15 11:00:00 | 300 | 450 |

## Related articles

* **Stages**: [`windowcomp`](../stages/windowcomp), [`comp`](../stages/comp), [`sort`](../stages/sort)
* **Functions**: [`sum (comp)`](sum_with_comp_stage), [`count()`](count_with_windowcomp_stage), [`avg()`](avg_with_windowcomp_stage)
* **Datasets**: [`xdr_data`](https://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction)