In this article, we will see how to count consecutive rows in SQL. This is one of the most important and commonly used real time SQL concept.
At the end of this article I have captured the video tutorial for your better understanding. So make sure to watch that video as well.
SQL count consecutive occurrences
Now we will discuss sql count consecutive occurrences with practical example.
Let us consider the Bus ticket booking system for this example.
Sometimes we want to book the tickets only when two consecutive seats are available or three or more consecutive bookings are available depending on our need.
Just observe the below booking table.
Now my requirement here is I want to book the seats where three consecutive seats are available.
I want to know the what are all the seat numbers available for me.
Let us get into the technical part now.
sql query to find consecutive records.
Now let us see what are all the technical things I need to frame the SQL.
SQL to find consecutive records
As I said I want to find where three consecutive records are available.
For that I need
- Current seat number , Previous Seat Number and Next Seat Number means seat number-1,seat number, seat number+1
- Available seat number count should be three for that I need count of seat numbers.
- I don’t need all the seat numbers I need only count of seat numbers where one preceding and one following is available .
The one preceding and one following is the concept of Window functions in SQL. In case you want to know more about this topic you check our window functions article.
Now let us make it very simple .
I want to know the seat numbers where three consecutive rows are available.
Let me write a query for this
Select seatno, availability, count ( case when availability = 'Y' then 1 end ) over (order by seatno rows range between 1 preceding and 1 following) cnt from booking
Output for this query will be.
Remember here the count is one preceding and one following .
Use of preceding and following to count consecutive occurrences
When you are in the first row there will not be any preceding row you will only have the following row.
That is why the first row count is 2 . Similarly for the last row you will have only preceding row and no following row. So the count would be 2 for last row.
When you are in the middle row you will have preceding row and following row. So here your count will be three.
Say for example you are using unbounded preceding and unbounded following means your count will 3 for all three rows as given in the below diagram.
I hope you are clear up to here.
Not only the SQL count max occurrences. You can modify your query accordingly for the following.
- Months: SQL count consecutive months
- Dates: SQL count consecutive dates
- Days: SQL consecutive days
You can also do the consecutive sum in SQL server using this method.
OK . Now let us complete the query to capture the records what we wanted.
select seatno-1, seatno, seatno+1 from ( select seatno, availability, count ( case when availability = 'Y' then 1 end ) over (order by seatno rows range between 1 preceding and 1 following) cnt from booking ) where cnt = 3;
Results for this query will be.
Similarly you can also write queries to find the longest consecutive series of events in SQL.
I hope this article is useful to you. Please leave your comments.