Aggregate functions based on current Row value

Question

I am working with data similar to below,

week | product | sale    
1    | ABC     | 2
1    | ABC     | 1
2    | ABC     | 1
3    | ABC     | 5
4    | ABC     | 1
2    | DEF     | 5

Let us say that is my Orders table named tblOrders. Now, in each row, I want to aggregate the total sales from last week for that product - for instance, if I am on week 2 of product "ABC", I need to show the aggregated sales amount of week 1 for product ABC. so, the output should look something like below,

week | product | sale    | ProductPreviousWeekSales
1    | ABC     | 2       | 0
1    | ABC     | 1       | 0
2    | ABC     | 1       | 3
3    | ABC     | 5       | 1
4    | ABC     | 1       | 5
2    | DEF     | 5       | 0

I was originally thinking I could solve this using Aggregates and Window Function, but doesn't look to be so. Another thought I was having is to use Conditional Aggregate - something like sum(case when x=currentRow.x then sale else 0 end), but that wouldn't work too.

Here is the SQLFiddle for above sample - http://sqlfiddle.com/#!18/890b7/2

Note: I need to calculate similar value for Last 4 weeks, so trying to avoid doing this as a sub-query or multiple joins (if possible), as the data set I am working with is very large, and don't want to add to much performance overhead trying to incorporate this change.

Answer 1

Here is one approach which first aggregates your table in a separate CTE and uses LAG to find the previous week's amount, for each week and product:

WITH cte AS (
    SELECT week, product,
           LAG(SUM(sale)) OVER (PARTITION BY product ORDER BY week) AS lag_total_sales
    FROM yourTable
    GROUP BY week, product
)

SELECT t1.week, t1.product, t1.sale,
       COALESCE(t2.lag_total_sales, 0) AS ProductPreviousWeekSales
FROM yourTable t1
INNER JOIN cte t2
    ON t2.week = t1.week AND
       t2.product = t1.product
ORDER BY
    t1.product,
    t1.week;

Demo

Answer 2

DISCLAIMER

The query I am showing below doesn't work in SQL Server, unfortunately. Up to SQL Server version 2019 the DBMS lacks full support of the RANGE clause that is essential for the query to work. Running the query in SQL Server results in

Msg 4194 Level 16 State 1 Line 1 RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters.

I am not deleting this answer, because this is standard SQL and the approach may help future readers. It runs fine in a lot of DBMS, and maybe a future version of SQL Server will be able to deal with this, too. I've added demos to show that it runs in PostgreSQL, MySQL and Oracle, but fails in SQL Server 2019.

---

ORIGINAL ANSWER

Your query shown in the fiddle (select a.*, sum(sale) over(partition by product) ProductPreviousWeekSales from tblOrder a) is merely lacking the appropriate windowing clause. As you are dealing with ties here (more than one row per product and week) this needs to be a RANGE clause:

select a.*,
  sum(sale) over(partition by product 
                 order by week range between 1 preceding and 1 preceding
                ) as ProductPreviousWeekSales
from tblOrder a
order by product, week;

(Use COALESCE if you want to see a zero instead of NULL.)

Demos:

• https://dbfiddle.uk/?rdbms=postgres_13&fiddle=149eddbff82500d539b2c615f4167cff
• https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a8453970efac08ad69275914910bb13e
• https://dbfiddle.uk/?rdbms=oracle_18&fiddle=64ed21150142caa0acb7f8c7ca7d9022
• https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=149eddbff82500d539b2c615f4167cff