How to insert old data to AggregatingMergeTree for AggregateFunction(count) column type in Clickhouse?

I have old table:

CREATE TABLE old_stats
(
    id String,
    results_count UInt64
)
ENGINE = AggregatingMergeTree
PRIMARY KEY (id);

New aggregating table:

CREATE TABLE new_stats
(
    id String,
    results_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PRIMARY KEY (id);

How directly insert into new_stats from old_stats

INSERT INTO new_stats
SELECT
id,
result_count
FROM old_stats;

Not working solutions:

countState(result_count) as result_count makes exception Conversion from AggregateFunction(count, UInt8) to AggregateFunction(count) is not supported

arrayReduce('countState', range(1, results_count)) makes exception Conversion from AggregateFunction(count, UInt8) to AggregateFunction(count) is not supported

If count is just a running total of integers, use SimpleAggregateFunction:

CREATE TABLE new_stats
(
    `id` String,
    `results_count` SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
PRIMARY KEY id

Notice the result from the following inserts:

insert into old_stats values (1, 200);
insert into new_stats values (1,10);

SELECT * FROM new_stats FINAL

Query id: 718c2240-97ca-431c-b8cf-9690961100ad

┌─id─┬─results_count─┐
│ 1  │           210 │
└────┴───────────────┘

And...your query above will work fine (where you insert the old table into the new one)

I just realized the answer as I was working on a different project. Your column in the MV is of type AggregateFunction(count), so you need to use the countState function to insert values into it.

Try the following query (it worked in a simple test I just ran):

INSERT INTO new_stats SELECT
    id,
    countState(results_count) AS results_count
FROM old_stats
GROUP BY id