sumSimpleState

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/examples/aggregate_function_combinators/sumSimpleState.md)

## Description[​](#description "Direct link to Description")

The [`SimpleState`](/docs/sql-reference/aggregate-functions/combinators#-simplestate) combinator can be applied to the [`sum`](/docs/sql-reference/aggregate-functions/reference/sum)
function to return the sum across all input values. It returns the result with
type [`SimpleAggregateFunction`](/docs/sql-reference/data-types/simpleaggregatefunction).

## Example usage[​](#example-usage "Direct link to Example usage")

### Tracking upvotes and downvotes[​](#tracking-post-votes "Direct link to Tracking upvotes and downvotes")

Let's look at a practical example using a table that tracks votes on posts.
For each post, we want to maintain running totals of upvotes, downvotes, and an
overall score. Using the `SimpleAggregateFunction` type with sum is suited for
this use case as we only need to store the running totals, not the entire state
of the aggregation. As a result, it will be faster and won't require merging
of partial aggregate states.

First, we create a table for the raw data:

```
CREATE TABLE raw_votes
(
    post_id UInt32,
    vote_type Enum8('upvote' = 1, 'downvote' = -1)
)
ENGINE = MergeTree()
ORDER BY post_id;
```

Next, we create a target table which will store the aggregated data:

```
CREATE TABLE vote_aggregates
(
    post_id UInt32,
    upvotes SimpleAggregateFunction(sum, UInt64),
    downvotes SimpleAggregateFunction(sum, UInt64),
    score SimpleAggregateFunction(sum, Int64)
)
ENGINE = AggregatingMergeTree()
ORDER BY post_id;
```

We then create a materialized view with `SimpleAggregateFunction` type columns:

```
CREATE MATERIALIZED VIEW mv_vote_processor TO vote_aggregates
AS
SELECT
  post_id,
  -- Initial value for sum state (1 if upvote, 0 otherwise)
  toUInt64(vote_type = 'upvote') AS upvotes,
  -- Initial value for sum state (1 if downvote, 0 otherwise)
  toUInt64(vote_type = 'downvote') AS downvotes,
  -- Initial value for sum state (1 for upvote, -1 for downvote)
  toInt64(vote_type) AS score
FROM raw_votes;
```

Insert sample data:

```
INSERT INTO raw_votes VALUES
    (1, 'upvote'),
    (1, 'upvote'),
    (1, 'downvote'),
    (2, 'upvote'),
    (2, 'downvote'),
    (3, 'downvote');
```

Query the materialized view using the `SimpleState` combinator:

```
SELECT
  post_id,
  sum(upvotes) AS total_upvotes,
  sum(downvotes) AS total_downvotes,
  sum(score) AS total_score
FROM vote_aggregates -- Query the target table
GROUP BY post_id
ORDER BY post_id ASC;
```

```
┌─post_id─┬─total_upvotes─┬─total_downvotes─┬─total_score─┐
│       1 │             2 │               1 │           1 │
│       2 │             1 │               1 │           0 │
│       3 │             0 │               1 │          -1 │
└─────────┴───────────────┴─────────────────┴─────────────┘
```

## See also[​](#see-also "Direct link to See also")

- [`sum`](/docs/sql-reference/aggregate-functions/reference/sum)
- [`SimpleState combinator`](/docs/sql-reference/aggregate-functions/combinators#-simplestate)
- [`SimpleAggregateFunction type`](/docs/sql-reference/data-types/simpleaggregatefunction)

- [Description](#description)- [Example usage](#example-usage)
    - [Tracking upvotes and downvotes](#tracking-post-votes)- [See also](#see-also)

Was this page helpful?