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 :
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.