Count Number of Consecutive Occurrence of values in Table

by

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.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. get a comma delimited string from rows [duplicate]
  3. Scope of temporary tables in SQL Server
  4. Sequence vs identity
  5. How to correctly insert newline in nvarchar [duplicate]
  6. Find min and max for subsets of consecutive rows – gaps and islands
  7. How to get Previous Value for Null Values
  8. SQL Server: +(unary) operator on non-numeric Strings
  9. SQL where clause odd behavior [closed]
  10. How do I escape a single quote in SQL Server?
  11. Simple way to transpose columns and rows in SQL?
  12. Only inserting a row if it’s not already there
  13. Unpivot with column name
  14. Selecting data from two different servers in SQL Server
  15. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  16. Most efficient way in SQL Server to get date from date+time?
  17. Convert varchar into datetime in SQL Server
  18. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  19. SQL : BETWEEN vs =
  20. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  21. How do I perform a GROUP BY on an aliased column in SQL Server?
  22. Need to list all triggers in SQL Server database with table name and table’s schema
  23. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  24. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  25. Get previous and next row from rows selected with (WHERE) conditions
  26. Best equivalent for IsInteger in SQL Server
  27. Syntax of for-loop in SQL Server
  28. Can SQL Server Express LocalDB be connected to remotely?
  29. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  30. Sql Server : How to use an aggregate function like MAX in a WHERE clause

Leave a Comment