Filtering within an window function (over ... partition by)?

Question

I am trying to use a sum() over (partition by) but filter within that summing. My use case is summing trailing twelve months up to a single month's entry for each product, so:

ITEM    MONTH    SALES
Item A  1/1/2011     2
Item A  2/1/2011     5
Item A  3/1/2011     3
Item A  4/1/2011     7
Item A  5/1/2011    12
Item A  6/1/2011     8
Item A  7/1/2011     9
Item A  8/1/2011    15
Item A  9/1/2011     6
Item A  10/1/2011    7
Item A  11/1/2011   12
Item A  12/1/2011    1
Item A  1/1/2012     3
Item A  2/1/2012     4
Item A  3/1/2012     5
Item A  4/1/2012     6
Item A  5/1/2012     4
Item A  6/1/2012     8
Item A  7/1/2012     9
Item A  8/1/2012    12
Item A  9/1/2012    14
Item A  10/1/2012    8
Item A  11/1/2012   12
Item A  12/1/2012   16

Would then return:

ITEM      MONTH_BEGIN SALES TTM SALES
Item A    1/1/2012        3        87
Item A    2/1/2012        4        88
Item A    3/1/2012        5        87
Item A    4/1/2012        6        89

Where the TTM SALES for 1/1/12 is the sum of 1/1/11-12/1/11

Answer 1

If you're open to anything other than an analytic SUM() then here is a possible solution with a simple correlated subquery

SELECT
    s.item,
    s.month AS month_begin,
    s.sales,
    (
        SELECT
            SUM(sales) AS sub_total
        FROM
            sales
        WHERE
            month BETWEEN DATEADD(month, -12, s.month) 
        AND DATEADD(month, -1, s.month)
    ) AS ttm_sales
FROM
    sales AS s
WHERE
    s.month BETWEEN '20120101' AND '20121201';

Sample output:

item	month_begin	sales	ttm_sales
Item A	January, 01 2012 00:00:00	3	87
Item A	February, 01 2012 00:00:00	4	88
Item A	March, 01 2012 00:00:00	5	87
Item A	April, 01 2012 00:00:00	6	89

Here is SQLFiddle demo.

Answer 2

The query below shows how I would do it with Oracle Analytic Functions:

SELECT
    "ITEM",
    TO_CHAR("MONTH", 'MM/DD/YYYY') AS "MONTH_BEGIN",
    "SALES",
    SUM("SALES") OVER (PARTITION BY "ITEM" ORDER BY "MONTH" 
        RANGE BETWEEN 
            INTERVAL '12' MONTH PRECEDING
            AND 
            INTERVAL '1' MONTH PRECEDING
    ) AS "TTM_SALES"  
FROM
    "SALES"  
ORDER BY
    "MONTH";

Working SQLFiddle demo

---

This will compute the sum function over a window that starts 12 months before the month of the current row and ends 1 month before it.

I assumed that you do not need to filter anything in the WHERE clause. If you do, be careful with it. Quoting the Oracle documentation:

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed.

So let's say that you want to display results only for the first quarter of 2012; if you try to do so by filtering in the WHERE clause, it will affect the cumulative results of TTM_SALES as well (outputing null, 3, 7 and 12).

The bottom line here is: If you need to filter out rows within the window of the analytic function, move the analytic function to a subquery and filter in the outer query as per @peterm's answer:

SELECT 
    "X"."ITEM",
    TO_CHAR("X"."MONTH", 'MM/DD/YYYY') AS "MONTH_BEGIN",
    "X"."SALES",
    "X"."TTM_SALES"
FROM
    (
        SELECT
            "ITEM",
            "MONTH",
            "SALES",
            SUM("SALES") OVER (PARTITION BY "ITEM" ORDER BY "MONTH" 
                RANGE BETWEEN 
                    INTERVAL '12' MONTH PRECEDING
                    AND 
                    INTERVAL '1' MONTH PRECEDING
            ) AS "TTM_SALES"  
        FROM
            "SALES"  
        ORDER BY
            "MONTH"
    ) AS "X"
WHERE 
    EXTRACT(MONTH FROM "X"."MONTH") BETWEEN 1 AND 4
AND EXTRACT(YEAR FROM "X"."MONTH") = 2012;