2

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.

2

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;
0

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)

  • Thanks! I wasn't quite able to use it exactly as described above because this Query Visualization Tool does not seem to allow View. But it did get me to start thinking about a different approach! – user132928 Aug 21 '17 at 20:29
-1
SELECT id,
       strftime('%Y-%m-%d %H:%M', tarih / 1000, 'unixepoch', 'localtime') alis_tarihi,
       alis_fiyati,
       kalan_adet,
       adet,
       kdv,
       SUM (adet) OVER (
                        ORDER BY tarih)
FROM satin_alma
WHERE urun_satis_durumu IN (0, 1)
  AND strftime('%Y-%m-%d %H:%M', tarih / 1000, 'unixepoch', 'localtime') < '2019-04-29 09:45'
18  2019-04-28 02:14    25  30  30  8   30
5   2019-04-28 11:17    30  20  25  4   55
12  2019-04-29 09:43    40  20  20  4   75
  • Welcome on the site! Note, code-only answers are typically not a very high quality. Please explain, what you did and how. – peterh May 4 at 12:19
-4

you can simply do like this

select order_date, count(order_date) from orders
group by order_date
  • 2
    Your query only calculates the number of rows with a matching date, not the total over time. For example, if you have a count of 2 each day, after 3 days the query would show 6 for the third day, not 2. – Tony Feb 16 at 10:52
  • Welcome to DBA.SE, thank you for taking the time to answer! Please could you edit your answer so that it provides a solution to the problem? – Mr.Brownstone Feb 17 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.