Count number of orders over date

I have a table like this: orders(id, created_ts, status)
I want to generate a MySQL query which shows the number of open orders with created_ts.
created_ts is MySQL timestamp

Sample data:

id created_ts status
--- ---------- -------
1 11-1-2017 Open
2 11-1-2017 Open
3 12-1-2017 Open
4 13-1-2017 Open
5 13-1-2017 Closed
6 14-1-2017 Closed


Outpur:

created_ts count
-------- ------
11-1-2017 2
12-1-2017 3
13-1-2017 4
14-1-2017 4


Where count is the number of orders opened for that date.

It is calculated as follows.

count of orders for a date = all orders with status = 'Open' with created_ts <= created_ts of that row.

I don't know how to approach the problem.
could anyone please help me.

created_ts <= created_ts

3 Answers
3

It seems you need cumulative sum:

SELECT t.created_ts,
(@running_total := @running_total + count(case when status='Open'then t.id else null end)) AS countval
FROM TABLE t
JOIN (SELECT @running_total := 0) r
group by t.created_ts
ORDER BY t.created_ts


You could use a correlated query to get running total of opened orders for distinct dates

select t1.created_ts,
(select count(*)
from demo
where created_ts <=t1.created_ts
and status = 'Open') date_wise_sum
from (
select distinct created_ts
from demo
) t1


Demo

If I understand correctly, we look at open records only and ignore the closed records. You want a running total, which you get with a windows function, available as of MySQL 8:

select created_ts, sum(sum(status = 'Open')) over (order by created_ts) as cnt
from mytable
group by created_ts
order by created_ts;


In earlier versions you can for instance join the counts to the dates:

select dates.created_ts, sum(counted.cnt) as total
from
(
select distinct created_ts
from mytable
) dates
join
(
select created_ts, count(*) as cnt
from mytable
where status = 'Open'
group by created_ts
) counted on counted.created_ts <= dates.created_ts
group by dates.created_ts
order by dates.created_ts;


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.