15,960,886 members
See more:
I have a table one in which there are various attribute like region product,year,qtr,month,sale. I have to calculate the avg_qtr sale of each product having same region and show their previous avg_qtr sale.I have read about lag but here it is not possible to use as it is not fixed after how many rows it will be repeated. My table structure is like this

Region Product Year Qtr Month Sales

NORTH P1 2015 1 JAN 1000
NORTH P1 2015 1 FEB 2000
NORTH P1 2015 1 MAR 3000
NORTH P1 2015 2 APR 4000
NORTH P1 2015 2 MAY 5000
NORTH P1 2015 2 JUN 6000
NORTH P1 2015 3 JUL 7000
NORTH P1 2015 3 AUG 8000
NORTH P1 2015 3 SEP 9000
NORTH P1 2015 4 OCT 1000
NORTH P1 2015 4 DEC 4000
NORTH P1 2015 4 NOV 2000
NORTH P3 2015 1 FEB 1000
NORTH P3 2015 1 FEB 9000
NORTH P3 2015 2 APR 2000
NORTH P3 2015 3 JUL 8000
NORTH P1 2016 1 MAR 3000
NORTH P1 2016 1 FEB 1000
NORTH P1 2016 1 JAN 2000
SOUTH P1 2015 1 JAN 2000
SOUTH P1 2015 1 FEB 3000
SOUTH P1 2015 1 JAN 4000
SOUTH P2 2015 1 MAR 1000
SOUTH P2 2015 1 JAN 8000
SOUTH P2 2015 1 FEB 9000
SOUTH P2 2015 2 JUN 9000
SOUTH P2 2015 2 MAY 8000
SOUTH P2 2015 2 APR 2000
SOUTH P2 2015 3 SEP 4000
SOUTH P2 2015 3 AUG 2000
SOUTH P2 2015 3 JUL 1000
SOUTH P2 2015 4 NOV 2000
SOUTH P2 2015 4 DEC 1000
SOUTH P2 2015 4 OCT 5000
SOUTH P3 2015 3 AUG 9000
SOUTH P3 2015 4 OCT 1000
SOUTH P3 2015 4 NOV 3000
SOUTH P2 2016 1 JAN 2000
SOUTH P2 2016 1 JAN 4000

I wrote the query which calculates current qtr and is showing previous one avg with current one
SQL
```  WITH AvgSales
AS (SELECT
region,
product,
year,
qtr,
ROUND(AVG(sales), 2) AS avg_Sale
FROM one
GROUP BY region,
product,
year,qtr
)
SELECT
s.region,
s.product,
s.year,
s.month,
s.sales,
avg.qtr,
avg.avg_Sale AS Qtr_Avg_Sale,
prev.avg_sale AS Prev_Qtr_Avg_Sale
FROM one s
JOIN AvgSales avg
ON s.region = avg.region
AND s.product = avg.product
AND s.QTR = avg.qtr
AND s.year = avg.year
LEFT JOIN AvgSales prev
ON  (s.region = prev.region
AND s.product = prev.product
AND s.year - 1 = prev.year
and s.qtr=1
AND prev.qtr = 4) or
(s.region = prev.region
AND s.product = prev.product
AND s.year = prev.year
AND s.qtr - 1 = prev.qtr) ;
```

I am able to get current average and previous average of that product but not vice versa. I am not sure how to show the previous average of that quarter which does not have any sale in current quarter.
I want a output like this-

Region Product Year qtr month sale avg_Sale prev_avg_sale
NORTH P1 2015 1 JAN 1000 2000
NORTH P1 2015 1 FEB 2000 2000
NORTH P1 2015 1 MAR 3000 2000
NORTH P1 2015 2 APR 4000 5000 2000
NORTH P1 2015 2 MAY 5000 5000 2000
NORTH P1 2015 2 JUN 6000 5000 2000
NORTH P1 2015 3 JUL 7000 8000 5000
NORTH P1 2015 3 AUG 8000 8000 5000
NORTH P1 2015 3 SEP 9000 8000 5000
NORTH P1 2015 4 OCT 1000 2333.33 8000
NORTH P1 2015 4 NOV 2000 2333.33 8000
NORTH P1 2015 4 DEC 4000 2333.33 8000
SOUTH P2 2015 1 JAN 8000 6000
SOUTH P2 2015 1 FEB 9000 6000
SOUTH P2 2015 1 MAR 1000 6000
SOUTH P2 2015 2 APR 2000 6333.33 6000
SOUTH P2 2015 2 MAY 8000 6333.33 6000
SOUTH P2 2015 2 JUN 9000 6333.33 6000
SOUTH P2 2015 3 JUL 1000 2333.33 6333.33
SOUTH P2 2015 3 AUG 2000 2333.33 6333.33
SOUTH P2 2015 3 SEP 4000 2333.33 6333.33
SOUTH P2 2015 4 OCT 5000 2666.67 2333.33
SOUTH P2 2015 4 NOV 2000 2666.67 2333.33
SOUTH P2 2015 4 DEC 1000 2666.67 2333.33
NORTH P3 2015 1 FEB 9000 5000
NORTH P3 2015 1 FEB 1000 5000
NORTH P3 2015 2 APR 2000 2000 5000
NORTH P3 2015 3 JUL 8000 8000 2000
SOUTH P3 2015 3 AUG 9000 9000
SOUTH P3 2015 4 OCT 1000 2000 9000
SOUTH P3 2015 4 NOV 3000 2000 9000
NORTH P1 2016 1 JAN 2000 2000 2333.33
NORTH P1 2016 1 FEB 1000 2000 2333.33
NORTH P1 2016 1 MAR 3000 2000 2333.33
NORTH P2 2016 2 2000
SOUTH P2 2016 1 JAN 2000 3000 2666.67
SOUTH P2 2016 1 JAN 4000 3000 2666.67
SOUTH P2 2016 2 3000
SOUTH P1 2015 1 JAN 4000 3000
SOUTH P1 2015 1 JAN 2000 3000
SOUTH P1 2015 1 FEB 3000 3000
Posted
Updated 3-Sep-15 21:45pm
v3

## Solution 1

Try this:
SQL
```SELECT
(SELECT AVG(Sales) FROM product WHERE year = @prevYear AND @QTR=@prevQtr) AS PrevQtr,
(SELECT AVG(Sales) FROM product WHERE year = @currYear AND @QTR=@currQtr) AS CurrQtr```

Replace `@prevYear`, `@prevQtr`, `@currYear` and `@currYear` with corresponding values.

Another option is to use Pivot[^] table.

[no name] 2-Sep-15 1:09am
how it can be done by pivot??
[no name] 2-Sep-15 1:21am
in this solution also how will I get every quarter avg value with current quarter
Maciej Los 2-Sep-15 1:59am
[no name] 2-Sep-15 2:01am
I don't find any link
[no name] 2-Sep-15 2:00am
where is the link?

## Solution 3

You have two problems that needs to be solved here.
1. Your table definition sucks.
You have three different temporal types that doesn't sort properly.
2. You have gaps in the sequence.

So lets fix them.
first we need to fix the temporal types
SQL
```SELECT  region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM    one
```
That was easy, filling the gaps is worse.
First we find the first and last dates used and then we create a sequence out of that.
SQL
```with fixeddate as (
SELECT  region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM    one
)
,daterange AS (
SELECT  Min(Trunc(saledate,'Q')) mindate
FROM    FixedDate
)
SELECT  Add_Months(mindate,LEVEL - 1) saledate
FROM    daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1```
Now we create a dummytable with all months and join with the fixed table
SQL
```with fixeddate as (
SELECT  region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM    one
)
,daterange AS (
SELECT  Min(Trunc(saledate,'Q')) mindate
FROM    FixedDate
)
,datesequence AS (
SELECT  Add_Months(mindate,LEVEL - 1) saledate
FROM    daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1
)
,products AS (
SELECT  DISTINCT
region
,product
FROM    one
)
,dummytable AS (
SELECT  region,product,saledate,0 sales
FROM    products p,datesequence ds
)
SELECT  d.region
,d.product
,d.saledate
,Sum(d.sales + Nvl(f.sales,0)) sales
FROM    dummytable d
left OUTER JOIN fixeddate f
ON  d.region = f.region
AND d.product = f.product
AND d.saledate = f.saledate
GROUP BY d.region
,d.product
,d.saledate```

Now we can query a table with a proper layout:
SQL
```with fixeddate as (
SELECT  region
,Product
,to_date(year || '-' || month,'YYYY-MON') saledate
,sales
FROM    one
)
,daterange AS (
SELECT  Min(Trunc(saledate,'Q')) mindate
FROM    FixedDate
)
,datesequence AS (
SELECT  Add_Months(mindate,LEVEL - 1) saledate
FROM    daterange dr
CONNECT BY Add_Months(mindate,LEVEL - 1) <= maxdate + 1
)
,products AS (
SELECT  DISTINCT
region
,product
FROM    one
)
,dummytable AS (
SELECT  region,product,saledate,0 sales
FROM    products p,datesequence ds
)
,fixedtableone AS (
SELECT  d.region
,d.product
,d.saledate
,Sum(d.sales + Nvl(f.sales,0)) sales
FROM    dummytable d
left OUTER JOIN fixeddate f
ON  d.region = f.region
AND d.product = f.product
AND d.saledate = f.saledate
GROUP BY d.region
,d.product
,d.saledate
)
,withavg AS (
SELECT  region
,product
,saledate
,sales
,Round(Avg(sales) OVER (PARTITION BY region,product,To_Char(saledate,'YYYY-Q') )) qtr_avg
FROM    fixedtableone
)
SELECT  region
,product
,To_Char(saledate,'YYYY') year
,To_Char(saledate,'Q') qtr
,To_Char(saledate,'MON') MONTH
,sales
,qtr_avg
,Lag(qtr_avg,3,0) OVER (PARTITION BY region,product ORDER BY saledate) prv_qtr_avg
FROM    withavg```

You can test the query here[^].
With a proper table layout you would have only needed the last two parts of the query and it would have scaled a lot better too.

v3
[no name] 4-Sep-15 3:46am
I have updated my question please check it
Jörgen Andersson 4-Sep-15 4:00am
Same principle, just add region wherever you have product in the query.
[no name] 4-Sep-15 4:29am
No its not showing what I want now...I want if there is no sale in current quarter than it should show current sale 0 and previous avg sale in other column but your code does not show any this solve this issue
Jörgen Andersson 4-Sep-15 4:33am
I don't understand, can you please elaborate
[no name] 4-Sep-15 4:38am
please look at my output how I want it there you will find in 4 last row there is something like this-
SOUTH P2 2016 2 3000
here in south region product P2 has sale in first quarter of 2016 but there was none sale in 2016 2 quarter so it should show current_sale=0 and previous sale=3000(from 1 quarter)

## Solution 4

```with qtrs(qtr) as (select level from dual connect by level &lt;= 4)
, t1 as (
select region, product, year, q.qtr, month, sales, year*4+q.qtr qord
from qtrs q
left join one partition by (region, product, year)
on q.qtr = one.qtr
)

select region
, product
, year
, qtr
, month
, sales
, round(avg(sales) over (partition by region, product, year,qtr),2) avg_sale
, round(avg(sales) over (partition by region, product
order by qord
range between 1 preceding
and 1 preceding),2) prev_avg_sale
from t1
order by year, region, qtr, product;```

v3
Jörgen Andersson 7-Sep-15 10:16am
A day when you learn something new is a good day.
I never knew about partitioned joins. Simplifies stuff immensely
[no name] 8-Sep-15 1:22am
^_^ thnks!!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 den2k88 50 merano99 50 Rick York 40 RickZeeland 30 OriginalGriff 30
 OriginalGriff 178 Pete O'Hanlon 130 merano99 80 den2k88 50 Rick York 50

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900