SQL query to aggregate by period through date

Question

I have a table that has transactional data by store number and transaction date. It also includes the year and period number of the corresponding transaction date, as such:

create table transactional_data (
    store_id number,
    tran_date date,
    year number,
    period_no number,
    orders number,
    sales number
);

I need a query that will return a row for every tran_date that includes a sum of orders and sales through that day of the period.

For example, if I had the following in the table:

STORE_ID    TRAN_DATE    YEAR    PERIOD_NO    ORDERS    SALES
10          2019-01-01   2019    1            10        119.12
10          2019-01-02   2019    1            15        201.56
10          2019-01-03   2019    1            13        167.92

I would want the result of the query to be:

STORE_ID    TRAN_DATE    YEAR    PERIOD_NO    ORDERS_PTD    SALES_PTD
10          2019-01-01   2019    1            10            119.12
10          2019-01-02   2019    1            25            320.68
10          2019-01-03   2019    1            38            488.60

I can easily accomplish a group by period to give me the total orders and sales for a store for the entire period, but I have not been able to find a way to break out the period to date sales through each transaction date.

How can this be accomplished?

Barbaros Özhan · Answer 1 · 2019-12-09T20:07:20.717

1

You can use sum() over (partition by .. order by ...) analytic function twice :

select t.*,
       sum(orders) over (partition by store_id order by tran_date) as orders_ptd,
       sum(sales) over (partition by store_id order by tran_date) as sales_ptd
  from transactional_data t
 order by tran_date;

Demo

edited Dec 09 '19 at 20:07

answered Dec 09 '19 at 20:02

Barbaros Özhan

59,113
10
31
55

score 0 · Answer 2 · answered Dec 09 '19 at 19:50

Do you just want a cumulative sum for each store during each period?

select t.*,
       sum(sales) over (partition by store_id, period_no order by trans_date)
from transactional_data t;

If you want to combine values on a given date, you can use aggregation:

select store_id, trunc(trans_date), year, period_no,
       sum(orders), sum(sales),
       sum(sum(sales)) over (partition by store_id, period_no order by trunc(trans_date)
from transactional_data t
group by store_id, trunc(trans_date), year, period_no;

score 0 · Answer 3 · answered Dec 09 '19 at 19:58

Try this:

select 10   as STORE_ID,       '2019-01-01' as TRAN_DATE,   2019  as YEAR,  1  as PERIOD_NO,          10 as ORDERS,       119.12 as SALES into #tmp
union select 10,          '2019-01-02',   2019,    1,            15,        201.56
union select 10,         '2019-01-03',   2019,    1,            13,        167.92

 select a.STORE_ID, a.TRAN_DATE, a.YEAR, a.PERIOD_NO, sum(b.ORDERS) as ORDERS_PTD    , sum(b.sales) as SALES_PTD
 from #tmp a inner join #tmp b on a.STORE_ID = b.STORE_ID and a.PERIOD_NO = b.PERIOD_NO and a.TRAN_DATE >= b.TRAN_DATE 
 group by a.STORE_ID, a.TRAN_DATE, a.YEAR, a.PERIOD_NO, a.[ORDERS]
 order by 2

SQL query to aggregate by period through date

3 Answers3