Running total with subgroups

user_12252327 0
Viewed 142

I am trying to get a running total by querying on an existing view, but I need precise groups first. I know I can do a running total using Sum(xx) over (Partition by xx Order by xx), but I only know how to use this in easy cases.

The data when running the view looks like this (with a few extra column that are useless), except without the Running_Stock column, which is what I'm trying to get :

+---------+----------+---------+------------+-------+---------+---------------+
| Product | Version  | Country |    Week    | sales | returns | Running_stock |
+---------+----------+---------+------------+-------+---------+---------------+
| Pdt1    | pdt1ver1 | Aus     | 2020M01W01 |    10 |       3 |             7 |
| pdt1    | pdt1ver1 | Fra     | 2020M01W01 |     8 |       2 |             6 |
| pdt1    | pdt1ver1 | Fra     | 2020M01W02 |    15 |       5 |            16 |
| pdt1    | pdt1ver2 | UK      | 2020M01W01 |    20 |       5 |            15 |
| pdt1    | pdt1ver2 | UK      | 2020M01W02 |    15 |       1 |            29 |
| pdt1    | pdt1ver2 | UK      | 2020M01w03 |     9 |       0 |            38 |
| pdt2    | pdt2ver1 | Fra     | 2020M01W01 |     5 |       1 |             4 |
| pdt2    | pdt2ver1 | Fra     | 2020M01W02 |     3 |       0 |             7 |
+---------+----------+---------+------------+-------+---------+---------------+

For each Product version in a given country, I should be able to get the running_stock at any point in time. The stock is calculated by doing "sales - returns".

I naïvely tought I could do a simple :

Select Product, Version, Country, Week, Sales, Returns,
Sum(sales-returns) over(partition by Version, Country Order by Week) Running_stock
from MyView

But all I get is for each " group ", the same running stock at every row, and it's not even the correct sum (it's actually the sum * 2).

teradata sql
1 Answers

You're close, but your current query is doing the SUM over all rows in each group. You just need to add a row specification for your cumulative sum:

Sum(sales-returns) over(
  partition by Version, Country 
  Order by Week 
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- all rows including current row
) Running_stock

TD Manual

user_87728880
Related