Select values calculated between rows

02 Jul

The example here uses 2 different approaches one using JOIN and another using LAG analytic function. In the table below I want to find the months that A store profiting more than B store. What I need is to first calculate the sum of monthly sales amount of each store and then subtract them. Note this table is only for demonstration hence it’s not normalized.


ID Store SaleMonth Amount
1 A Jan 120.00
2 A Feb 80.50
3 A Feb 200.00
4 B Jan 160.50
5 B Feb 70.00
6 A Feb 130.00
7 B Mar 180.00
8 A Mar 110.50

How I did it was primitive and the performance was terrible with numerous records. I calculated the sums of both stores and then joined them together so I can calculate the result by subtracting 2 columns:

SELECT t.SaleMonth, netamount
  FROM (SELECT A.SaleMonth, A.Profit - B.Profit netamount
          FROM (SELECT Sales.SaleMonth, SUM(Amount) AS Profit
                  FROM Sales
                 WHERE Sales.Store = 'A'
                 GROUP BY Sales.SaleMonth) A
          LEFT JOIN (SELECT Sales.SaleMonth, SUM(Amount) AS Profit
                      FROM Sales
                     WHERE Sales.Store = 'B'
                     GROUP BY Sales.SaleMonth) B
            ON A.SaleMonth= B.SaleMonth) t
 WHERE t.netamount > 0;

A better approach is using the Oracle LAG analytic function. First sum the Amount grouping by SaleMonth & Store and then subtract between rows using LAG. This is roughly 5 times faster than the previous one in other test case:

SELECT s.SaleMonth, netamount
  FROM (SELECT t.SaleMonth,
               t.Amount- LAG(Amount, 1) OVER(PARTITION BY Sales.SaleMonth
                                             ORDER BY Sales.SaleMonth, Sales.Store) netamount
          FROM (SELECT Sales.SaleMonth, Sales.Store, SUM(Amount) AS Amount
                  FROM Sales
                 GROUP BY Sales.SaleMonth, Sales.Store) t) s
 WHERE s.netamount > 0;

The query result:

SaleMonth netamount
Feb 340.50

Reference:LAG analytic function in Oracle SQL

Leave a comment

Posted by on July 2, 2012 in Database, Oracle, SQL


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: