I was wanting to create a running total of item count for any given month, I can get the count totals as shown below but not a cumulative total as shown in the second table below

SELECT day, count(items) from table1
GROUP BY day

        day       | count
----------------------------
2019-06-01  |     10
2019-06-02  |     10
2019-06-03  |     10


        day       | count
----------------------------
2019-06-01  |     10
2019-06-02  |     20
2019-06-03  |     30

Any ideas would be greatly appreciated

Anthony


Try

SELECT day, count(items), runsum(1) from table1
GROUP BY day



Roy Lambert

Just to point out an interesting fact

I didn't think a simple RUNSUM(1) would work so I tried a few ideas using one of my tables


select
count(*),
runsum(1),
(SELECT COUNT(*) FROM Analysis X WHERE X._fkCoding = Analysis._fkCoding),
RUNSUM((SELECT COUNT(*) FROM Analysis X WHERE X._fkCoding = Analysis._fkCoding))
from Analysis group by _fkcoding

The interesting thing is that runsum does its counting BEFORE the group by takes effect

Roy Lambert

Anthony

Another way of obtaining cumulative counts - and it is a sensitive query.

CREATE TABLE "SalesSummary"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"SaleDate" DATE NOT NULL,
"SaleCount" INTEGER DEFAULT 0 NOT NULL
)


CREATE VIEW "Sales1" AS
SELECT ID, SaleDate, SaleCount,
(SELECT SUM( SaleCount)
   FROM SalesSummary AS S1
   WHERE
     S0.SaleDate >= S1.SaleDate) AS NumberOfSales

FROM SalesSummary AS S0

=====================================================

SalesSummary
--------------------

"ID","SaleDate","SaleCount"
1,2019-06-01,10
2,2019-06-02,10
3,2019-06-03,10

Sales1
---------

"ID","SaleDate","SaleCount","NumberOfSales"
1,2019-06-01,10,10
2,2019-06-02,10,20
3,2019-06-03,10,30



Richard