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 concepts.
At the end of this article, I have provided 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 needs.
Just observe the below booking table captured from the ticket booking system. I got three columns Seat Number, Availability, and Bus Name. If can observe this table very closely you can see the row number 4, 5, and 6 are the 3 consecutive seats which are available for the booking.
Now my requirement here is I want to book the seats where three consecutive seats are available.
In other words I want to know what are all the seat numbers available for me where it has 3 consecutive seats available for booking.
Let us get into the technical part now. How we can write SQL query to find consecutive records.
First 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 capture where three consecutive records are available.
For that I need below things.
- Current seat number, Previous Seat Number and Next Seat Number means seat number-1, seat number, seat number+1
- The available seat number count should be three for that I need a count of seat numbers.
- I don’t need all the seat numbers I need the only count of seat numbers where one preceding and one following is available.
The one preceding and one following is the concept of Windows 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 means 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 the last row.
When you are in the middle row you will have preceding row and following row. So here your count will be 3.
Say for example you are using unbounded preceding and unbounded following means your count will be 3 for all three rows as shown 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.