Running Total of a count per day in SQLite

running-totalssqlite

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.

Best Answer

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;

Related Solutions

MySQL – Running Total: To Store or Compute

Like many things, the answer to this question is "it depends". In this case, what it depends on is how many transactions each product will get.

With low to moderate volumes of transactions, it will be very fast to compute the running total on the fly and you don't have to write lots of code to compute, store and maintain the totals.

Index your table!

Make sure you have an index on (or starting with) production_transaction.product_id so that you can quickly retrieve all transactions for a given product.
An index on the combination of product_id, transaction_date and a query specifying product_id and a date range (or >= today) will give you a day's transactions for a given product, and with good performance.

Test!

Be sure to test this in your Test / Q&A environment! Generate representative transaction loads, but also much higher loads to see how it will perform in the expected and unexpected cases.

Even at high transaction levels, you will have to weigh up the performance associated with updating and storing the running totals vs how often they are queried.

MySQL – Calculating Total Hours Worked Per Employee Per Day Including Overnight Sessions

Please check if the performance is sufficient:

SELECT empid
      ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                      FROM emplog b
                                                     WHERE b.empid = a.empid
                                                       AND b.time  > a.time
                                                       AND b.state = 1
                                                    )))) date_worked
  FROM emplog a
 WHERE state=0
 GROUP BY empid;

Edit

To aggregate per day:

SELECT empid
      ,DATE(time) day
      ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                      FROM emplog b
                                                     WHERE b.empid = a.empid
                                                       AND b.time  > a.time
                                                       AND b.state = 1
                                                    )))) date_worked
  FROM emplog a
 WHERE state=0
 GROUP BY empid, DATE(time);

Related Question