In this article, we’ll see the way to count consecutive rows in SQL with practical examples. this is often one of the foremost important and commonly used real-time SQL concepts.
I am sure you’ll get a clear picture of this subject so confirm to read this text till the end.
At the end of this article, I even have provided a video-based tutorial for your better understanding. So confirm to watch that video also.
How to count consecutive occurrences
Now we will discuss SQL count consecutive occurrences with practical examples.
Let us consider the ticket booking system for this instance.
Sometimes we would like to book the tickets only two consecutive seats are available or three or more consecutive bookings are available for our booking depending on our need.
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 that row numbers 4, 5, and 6 are the 3 consecutive seats that 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 would like to know all the seat numbers available for me there it’s 3 consecutive seats available for booking.
Let us get into the technical part now. How we can write SQL queries 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 would like to capture where three consecutive records are available.
For that, I need the 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 the preceding row and no following row. So the count would be 2 for the last row.
When you are within the first row there’ll not be any preceding row means you’ll only have the subsequent row.
Say for instance you’re using unbounded preceding and unbounded following means your count is going to be 3 for all three rows as shown within the below diagram.
I hope you are clear up 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 that 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 beneficial to you. Please leave your comments within the comment section below.