CODEWITHSUNDEEP
mysqlsql
shreyas d
shreyas d

Reputation: 774

How to track previous row status count

I want to calculate count of order status changes within different states. My Orderstatus table:

| id |ordr_id|   status   |
|----|-------|------------|
| 1  |   1   | pending    |
| 2  |   1   | processing |
| 3  |   1   | complete   |
| 4  |   2   | pending    |
| 5  |   2   | cancelled  |
| 6  |   3   | processing |
| 7  |   3   | complete   |
| 8  |   4   | pending    |
| 9  |   4   | processing |

Output I want:

|        state         | count |
|----------------------|-------|
| pending->processing  |   2   |
| processing->complete |   2   |
| pending->cancelled   |   1   |

Currently I'm fetching the results by SELECT order_id,GROUP_CONCAT(status) as track FROM table group by order_id and then process the data in php to get the output. But is that possible in query itself ?

Upvotes: 0

Views: 89

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use lag():

select prev_status, status, count(*)
from (select t.*,
             lag(status) over (partition by order_id order by status) as prev_status
      from t
     ) t
group by prev_status, status;

LAG() is available in MySQL starting with version 8.

Note that you can filter out the first status for each order by putting where prev_status is not null in the outer query.

Your version is not quite correct, because it does not enforce the ordering. It should be:

SELECT order_id,
       GROUP_CONCAT(status ORDER BY id) as track

EDIT:

In earlier versions of MySQL, you can use a correlated subquery:

select prev_status, status, count(*)
from (select t.*,
             (select t2.status
              from t t2
              where t2.order_id = t.order_id and t2.id < t.id
              order by t2.id desc
              limit 1
             ) as prev_status
      from t
     ) t
group by prev_status, status;

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

...or just a simple join...

SELECT CONCAT(a.status,'->',b.status) action
     , COUNT(*) total 
  FROM my_table a 
  JOIN my_table b 
    ON b.ordr_id = a.ordr_id 
   AND b.id = a.id + 1
 GROUP 
    BY action;
+----------------------+-------+
| action               | total |
+----------------------+-------+
| pending->cancelled   |     1 |
| pending->processing  |     2 |
| processing->complete |     2 |
+----------------------+-------+

Note that this relies on the fact that ids are contiguous.

Upvotes: 1

forpas
forpas

Reputation: 164054

With a join of the 3 status change types to the grouping of the table that you already did:

select c.changetype, count(*) counter
from (
  select 'pending->processing' changetype union all
  select 'processing->complete' union all
  select 'pending->cancelled'
) c inner join ( 
  select 
    group_concat(status order by id separator '->') changestatus
  from tablename  
  group by ordr_id  
) t on concat('->', t.changestatus, '->') like concat('%->', changetype, '->%')
group by c.changetype

See the demo.
Results:

> changetype           | counter
> :------------------- | ------:
> pending->cancelled   |       1
> pending->processing  |       2
> processing->complete |       2

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

If id column ensure the sequence of records, you can use self join to achieve your requirement as below-

SELECT A.Status +'>'+ B.Status, COUNT(*) 
FROM OrderStatus A
INNER JOIN OrderStatus B
ON A.id = B.id -1
WHERE B.Status IS NOT NULL
GROUP BY A.Status +'>'+ B.Status

Upvotes: 1

Related Questions