Counting occurrence a record based on consecutive days I have a query to display record of stationary issued according to date.

SELECT date,stationary_id FROM stationary_issued 


How do i check if a stationary was issued in consecutive days.I have tried group by and sum row.

Current table

date       stationary_id
2019-04-01 2
2019-04-01 3
2019-04-01 4
2019-04-01 5
2019-04-02 3
2019-04-02 2
2019-04-03 3


Expected

stationary_id consecutive_occurrence
3             3
2             2


I have tried grouping date and stationary while counting stationary_id but the result is not correct
I'd argue your results are actually...

stationary_id consecutive_occurrence
3             2
2             1


You have 3 days present. Only two of those days are consecutive (04/02 and 04/03), meaning there was a day preceding them.

I'm making a few assumptions...


Each record in the table is an occurrence; stationary was delivered that day
A record is marked as a consecutive delivery, if (and only if) there was a delivery the prior day.
Since we don't have data for 03/31, we can't say whether deliveries on 04/01 were consecutive.


Generating sample data matching your post...

create table #thinger ([date] date, stationary_id tinyint);
go
insert into #thinger ([date], stationary_id) values ('2019-04-01', 2);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 3);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 4);
insert into #thinger ([date], stationary_id) values ('2019-04-01', 5);
insert into #thinger ([date], stationary_id) values ('2019-04-02', 3);
insert into #thinger ([date], stationary_id) values ('2019-04-02', 2);
insert into #thinger ([date], stationary_id) values ('2019-04-03', 3);
go


Here is a quick and dirty query to retrieve the summation you asked for...

;WITH consecutive_stationary_orders AS (
    SELECT cday.[stationary_id]
         , (
            select 1 
              from #thinger pday
             where pday.[date] = DATEADD(day, -1, cday.[date]) 
               AND pday.stationary_id = cday.stationary_id
           ) AS was_consecutive
      from #thinger cday
)
SELECT [stationary_id], SUM(was_consecutive)
  FROM consecutive_stationary_orders
 GROUP BY [stationary_id]
HAVING SUM(was_consecutive) IS NOT NULL;


Forgive me. I used SQL/T-SQL syntax as I don't have a MySQL instance at this very second to use. But, this should give you an idea of what I'm doing.

First result set returns a list of dates, stationary issued (by ID), and whether that day's delivery was consecutive. The second query (wrapper) aggregates the first query. Whatever RDBMS you use, that's the approach I'd take.