SQL Query to Group / Aggregate N Consecutive Rows

Interview Question:

In one of my project, I got a requirement to group N consecutive rows and find the min, max and sum of values in each group. In this article, I will show you how to do aggregation on N successive rows.

Let’s take the sales table as an example. The data in the source (sales) table is shown below:

Table Name: Sales

Sales_Key Price
---------------
1   10
2   20
3   30
------------
4   40
5   50
6   60
------------
7   80
8   90

Here, I have to group 3 (N) consecutive records and then find the sum of the price within each group. In the above data, the rows to be grouped are separated with dotted lines. The output is shown below:

Group_Price
-----------
60      --Sum of first 3 rows
150     --Sum of fourth, fifth and sixth rows
170     --Sum of seventh and eight row.

At first, I thought, this cannot be implemented using SQL query and wrote an oracle PLSQL procedure. Later after trying for some time I got the solution using the SQL query. Here, I am providing the SQL query, so that if you get such a requirement it will be easy for you to implement.

SQL Query to Aggregate Rows:

The following SQL query aggregates 3 (N) consecutive rows and computes the sum of the price:

SELECT  SUM(s.price)
FROM    sales s
 LEFT OUTER JOIN
        (
  SELECT s1.sales_key min_range, s2.sales_key max_range
  FROM sales s1,
   sales s2
  WHERE s2.sales_key-s1.sales_key = 3-1 -- N-1 
  AND MOD(s2.sales_key,3) IN (0,1) 
        ) r
ON (s.sales_key BETWEEN r.min_range AND r.max_range
Group   By  NVL(r.min_range,-1)