Vryjdfkk

I usually write a couple of very simple queries on a redshift database which used in a marketing visualization tool. And I repeat - VERY simple - I am a novice at this.

I am now faced with a new data source (SQLite) and I'm stuck.

I have a table with :

• order_id


• order_date

where there are several orders per day. And I am trying to get a running total of the daily count of order ids.

For example the table would contain :

order_id      order_date  

2541          2017-06-05  

26777         2017-06-05  

123           2017-06-06  

8795          2017-06-07  

And I'm trying to get to this result :

Day          RunningTotal  

2017-06-05 : 2  

2017-06-06 : 3  (the 2 of the previous day + 1 this day)  

2017-06-07 : 4  

In POSTGRESQL i would use

SELECT  

order_date,  

SUM (COUNT(order_id)) OVER (ORDER BY order_date rows between unbounded preceding and current row) as RunningTotal

FROM table  

ORDER BY  

order_date  

GROUP BY  

order_date  

How do I do this in SQLite?

I've googled - and see many examples of either SUM or COUNT but seldom combined and never to achieve a running total of a count.

For anyone trying something similar : this is what worked in the end :

select

a.ORDER_DATE,

sum(b.ID_COUNT) RunningTotal

from

(select

ORDER_DATE, Count(DISTINCT(SOFTWARE_ID)) ID_COUNT

from orders

group by ORDER_DATE

order by ORDER_DATE) a,

(select

ORDER_DATE, Count(DISTINCT(SOFTWARE_ID)) ID_COUNT

from orders

group by ORDER_DATE

order by ORDER_DATE) b

where a.ORDER_DATE >= b.ORDER_DATE

group by a.ORDER_DATE

order by a.ORDER_DATE;

Maybe this will help: create a view that delivers the COUNT() for each day, then use this view (in a query) to calculate the running total (with SUM()):

create view order_view

as

select

  order_date

, count(order_id) order_count

from orders 

group by order_date 

order by order_date ; 

This delivers (using your test data):

select * from order_view

-- output

order_date  order_count

2017-06-05  2

2017-06-06  1

2017-06-07  1

Then, use something like ...

select 

  a.order_date

, sum(b.order_count) RunningTotal

from 

  order_view a

, order_view b

where a.order_date >= b.order_date

group by a.order_date

order by a.order_date;



-- output

order_date   RunningTotal

2017-06-05   2

2017-06-06   3

2017-06-07   4

See also: dbfiddle.
Note that "SQLite does not have a storage class set aside for storing dates and/or times." (see https://sqlite.org/datatype3.html)

you can simply do like this

select order_date, count(order_date) from orders

group by order_date