Analytics
Fan

## How to get previous quarter total (SQL)

Hello,

I have a table with Customer, Quarter, Month and Sales (montly) figures.

I need to calculate Quarter Sales and Previous Quarter Sales per Customer, while also showing Montly sales (see desired table format below).

For quarter sales I use sum() over partition by

`SelectCustomer, Quarter, Month, sum(sales) over (partition by Customer, Quarter order by Quarter) as QuarterSalesfrom TableA`

However, I am not able to find a way to get total for previous quarter. I suppose, I need to remove Quarter from partition by statement, but then I need to select only rows, where Quarter is equal Quarter - 1 somehow. Probably Reset When should help, but I am not able to make it work properly.

Thank you in advance,

Ivan

Table A:

 Customer Quarter Month Sales 1 1 1 0 1 1 2 100 1 1 3 200 1 2 4 300 1 2 5 100 1 2 6 50 2 1 1 100 2 1 2 0 2 1 3 100 2 2 4 50 2 2 5 200 2 2 6 300

Output: TableA + Calculated Quarterly totals:

 Customer Quarter Month Sales QuarterSales PreviousQuarterSales 1 1 1 0 300 1 1 2 100 300 1 1 3 200 300 1 2 4 300 450 300 1 2 5 100 450 300 1 2 6 50 450 300 2 1 1 100 200 2 1 2 0 200 2 1 3 100 200 2 2 4 50 550 200 2 2 5 200 550 200 2 2 6 300 550 200
Tags (3)
3 REPLIES
Fan

## Re: How to get previous quarter total (SQL)

Sorry, tables do not look nice:

TableA

Customer Quarter Month Sales

1             1          1        0

1             1          2        100

1             1          3        200

1             2          4        300

1             2          5        100

1             2          6        50

2             1          1        100

2             1          2        0

2             1          3        100

2             2          4        50

2             2          5        200

2             2          6        300

TableA + Quarter Totals

Customer Quarter Month Sales QuarterSales PreviousQuarterSales

1             1          1        0       300

1             1          2        100    300

1             1          3        200    300

1             2          4        300    450               300

1             2          5        100    450               300

1             2          6        50      450               300

2             1          1        100    200

2             1          2        0       200

2             1          3        100    200

2             2          4        50     550               200

2             2          5        200   550               200

2             2          6        300   550               200

Junior Contributor

## Re: How to get previous quarter total (SQL)

There's no way to get this result without nesting OLAP-functions.

Assuming there's always a row per customer/quarter:

`Select   Customer , Quarter , Month , QuarterSales , sum(QuarterSales)   over (partition by Customer         order by Month         rows between 3 preceding and 3 preceding) as QuarterSalesfrom (   Select      Customer    , Quarter    , Month    , sum(sales) over (partition by Customer, Quarter order by Quarter) as QuarterSales   from TableA ) as dt`
Fan

## Re: How to get previous quarter total (SQL)

Hi Dieter,

Thank you for a quick reply. I did not think in this direction. I am still learning how you can use partition by approach properly.

This does work, however, I was trying to avoid nesting OLAP-functions.

I just wonder, from performance point of view what is the preferred option to have nested OLAP function or to have a separate table (TableSum) with calculated quarter totals for each customer and then left join to the original table (TableA) on TableSum.Customer = TableA.Customer and TableSum.Quarter = TableA.Quarter-1.

So far I used second approach (left join) instead of nested OLAP-function.

I will test partition by approach to see the benefits.

Thank you again.

Ivan