我试图创建累积列,但无法创建它,请看一下我尝试计算它的方式。
我的表格
create table mytable (
TotalQuantity Decimal(7)
);
insert into mytable (TotalQuantity) values
(0.0),
(0.0),
(1.0),
(0.0),
(2.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(0.0),
(1.0),
(1.0),
(0.0),
(1.0);
根据上述数据集,我想要计算不同的计数和累计总和。
SELECT TotalQuantity AS DistinctTotalQuantity,
COUNT(TotalQuantity) AS COUNTVALUE,
@running_total := @running_total + COUNT(TotalQuantity) AS cumulative_sum
FROM mytable
JOIN (SELECT @running_total := 0) r
GROUP BY TotalQuantity