Thursday, March 14, 2013

SQL Query to Group or Aggregate N Consecutive Rows

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:
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 a 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
  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)

No comments:

Post a Comment