CODEWITHSUNDEEP
mysqlsql
Harikrishnan
Harikrishnan

Reputation: 3822

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.

Upvotes: 0

Views: 476

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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;

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

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

Upvotes: 2

Fahmi
Fahmi

Reputation: 37483

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

Upvotes: 1

Related Questions