How to Count Most Consecutive Occurrences of a Value in a Column in SQL Server

Count Number of Consecutive Occurrence of values in Table

One approach is the difference of row numbers:

select name, count(*) 
from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by name order by id)
             ) as grp
      from t
     ) t
group by grp, name;

The logic is easiest to understand if you run the subquery and look at the values of each row number separately and then look at the difference.

Count max number of consecutive occurrences of a value in SQL Server

You need a column to specify the ordering. SQL tables represent unordered sets. In the below query, the ? represents this column.

You can use the difference of row numbers to get each winning streak:

select player, count(*) as numwins
from (select t.*,
             row_number() over (partition by player order by ?) as seqnum, 
             row_number() over (partition by player, result order by ?) as seqnum_r 
      from t
     ) t
where result = 'W'
group by player, (seqnum - seqnum_r);

You can then get the maximum:

select player, max(numwins)
from (select player, count(*) as numwins
      from (select t.*,
                   row_number() over (partition by player order by ?) as seqnum, 
                   row_number() over (partition by player, result order by ?) as seqnum_r 
            from t
           ) t
      where result = 'W'
      group by player, (seqnum - seqnum_r)
     ) pw
group by player;

Find the Biggest Number of Consecutive Occurrence of values in Table

You can subtract an enumerated value for each name to get a constant for adjacent values that are the same. The rest is aggregation:

select top (1) name, count(*), min(id), max(id)
from (select l.*,
             row_number() over (partition by name order by id) as seqnum
      from #Launches l
     ) l
group by (id - seqnum), name
order by count(*) desc;

Here is a db<>fiddle.

Count consecutive rows for each customer and value

For gaps and islands solution, for first row_number you need to partition by customer.

SELECT customer,    status, COUNT(*) FROM (
select t.*,
             (row_number() over (partition by customer order by id) -
              row_number() over (partition by customer, status order by id)
             ) as grp
      from tickets t
) X
GROUP BY customer,  status, grp
ORDER BY customer,  max(id)

dbfiddle

Result:

customer    status  count
--------    ------  -----
A           0       4
A           1       3
B           0       2
B           1       1
B           0       2
C           0       1

How to count most consecutive occurrences of a value in a Column in SQL Server

Try this:-

The difference between the consecutive date will remain constant

   Select max(Sequence)
  from 
  (
   select present ,count(*) as Sequence,
         min(date) as MinDt, max(date) as MaxDt
         from (
                select t.Present,t.Date,
                    dateadd(day,
                              -(row_number() over (partition by present order by date))
                               ,date 
                          ) as grp
              from Table1 t
            ) t
  group by present, grp
  )a
   where Present ='Y'

SQL FIDDLE

How can I query for consecutive values above a specific count in SQL Server?

Adjusting your own solution but then using lag instead of joins:

WITH SUB AS 
  (SELECT id, 
   dummy_data, 
   lag(dummy_data, 1) OVER (ORDER BY ID) as dd1, 
   lag(dummy_data, 2) OVER (ORDER BY ID) as dd2
  FROM data_table)
SELECT ROW_NUMBER() OVER (ORDER BY id) AS id, dummy_data 
FROM SUB
WHERE dummy_data = dd1 AND dd1 = dd2

Count consecutive duplicate values in SQL

I am going to presume that id is unique and increasing. You can get counts of consecutive values by using the different of row numbers. The following counts all sequences:

select grp, value, min(id), max(id), count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by value order by id)
             ) as grp
      from table t
     ) t
group by grp, value;

If you want the longest sequence of 0s:

select top 1 grp, value, min(id), max(id), count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by value order by id)
             ) as grp
      from table t
     ) t
group by grp, value
having value = 0
order by count(*) desc

Count the number of consecutive Zeros along with their instances SQL

Gordin Linoff, your answer is nearly perfect. However, this will give the accurate results:

select  id
        ,BAL_CON_ZERO_INSTANCES+ (total_zeros-BAL_CON_ZERO_DAYS) AS BAL_CON_ZERO_INSTANCES
        ,BAL_CON_ZERO_DAYS
(
select id,

        ,sum(case when bal = 0 and (prev_bal = 0 or prev_bal is null) and (next_bal = 1 or next_bal is null) 
                then 1 else 0
            end)  as BAL_CON_ZERO_INSTANCES
        ,sum(case when (bal = 0 and (next_bal = 0 or next_bal is null) or (bal = 0 and (prev_bal = 0 or prev_bal is null) and (next_bal = 1 or next_bal is null))) then 1 else 0 end) as BAL_CON_ZERO_DAYS
        ,sum(case when bal = 0 then 1 else 0 end) as total_zeros

from (select t.*,
             bal,
             lag(bal) over (partition by id order by row_no) as prev_bal,
             lead(bal) over (partition by id order by row_no) as next_bal
      from t
     ) t
group by id;

---

Related Topics