All Forums Database
johnsnow 10 posts Joined 01/15
24 Apr 2015
aggregate function based on multiple rows

I am trying to perform a sum based on multiple values in a table.
 
The current year week is formated like this 201401 for 2014 year and week 1.

I dont know how to properly reference it to get the weekly sales to roll up for last year and stay in the row with the current week. The current week sales works with a simple SUM CASE WHEN statement but pulling last years is proving to be more difficult than i expected. Im trying not to have to replicate the query completley and join them if possible.
 
Example of current data table:
State   Store   YrWK       LYWK   Week   Year   Sales
A         1         201401   201301   1       2014   100
A         2         201401   201301   1       2014   200  
A         3         201301   201201   1       2013  150
 
Final would look like this:
State   Week   Year   TY_Sales   LY_Sales
A          1         2014   300           150

manib0907 61 posts Joined 04/15
24 Apr 2015

SELECT
STATE,
WEEK,
YEAR,
SUM(A.SALES) OVER(PARTITION BY A.YRWK) AS TYSALES
SUM(B.SALES) OVER(PARTITION BY B.YRWK) AS LYSALES
FROM TBL A
INNER JOIN TBL B
ON A.LYWK=B.YRWK
QUALIFY ROW_NUMBER() OVER(PARTITION BY STATE ORDER BY STORE)=1
I think this should work.

Cheers,
Mani

johnsnow 10 posts Joined 01/15
25 Apr 2015

I appreciate the quick response but There is no table B. My hope was to not have to create an additional table if possible  
I was thinking there was a function that would allow me to reference last year same week sales to sum up and apply to this year same week row in the final table. 

dnoeth 4628 posts Joined 11/04
25 Apr 2015

Can you show your actual query?
Do you need this result for a single year of multiple years?
Based on your description you will get the same week of the previous year using YrWK - 100
 

Dieter

manib0907 61 posts Joined 04/15
25 Apr 2015

There is no table B.Its a self join. :)

Cheers,
Mani

johnsnow 10 posts Joined 01/15
25 Apr 2015
Select
a.state
,a.yrwk as year_wk
,a.week
,a.yr
,sum(case when yrwk is not null then a.sales else 0 end) as Ty_sales
,sum(case when year_wk = a.Ly_wk then a.sales end) as ly_sales 
From a

group by 1,2,3,4

When I try year_wk = ly_wk it returns nulls (ly_wk is defined in table a as yrwk-100)
 
And the result needs to be for multiple years in the sense that the sales data for 2014 ly same week should show 2013. And the 2013 lywk sales should be from the same week in 2012

johnsnow 10 posts Joined 01/15
25 Apr 2015

Manib,
 
i tried it and got an error message that object table_b does not exist

VandeBergB 182 posts Joined 09/06
25 Apr 2015

Johnsnow,
Try this...(you'll need to change the table names, but it works...

 SELECT 
 	 a.state
 	,a.weeknum
 	,a.yearnum
 	,a.sales AS TY_sales
 	,b.Sales AS LY_Sales
  FROM 
 (SELECT 
 	 state
 	,yrwk
 	,lywk
 	,weeknum
 	,yearnum
    ,SUM(sales) AS sales
 FROM edw.sales
 GROUP BY 1,2,3,4,5)a
 INNER JOIN  
 (SELECT 
 	 state
 	,yrwk
 	,lywk
 	,weeknum
 	,yearnum
    ,SUM(sales) AS sales
 FROM edw.sales
 GROUP BY 1,2,3,4,5)b
  ON (a.state = b.state
  AND a.lywk = b.yrwk
  AND a.weeknum = b.weeknum)

The Data (select * from edw.sales order by store asc;)

	State	Store	YrWk	LyWk	WeekNum	YearNum	Sales
1	A 	1	201401	201301	1	2014	100
2	A 	2	201401	201301	1	2014	200
3	A 	3	201301	201201	1	2013	150

The results:

	State	WeekNum	YearNum	TY_sales LY_Sales
1	A 	1	2014	300	 150

 

Some drink from the fountain of knowledge, others just gargle.

johnsnow 10 posts Joined 01/15
25 Apr 2015

Thanks vandeberg,
 
that worked I'll just have to create an ad_hoc table I guess for the sales table so I'm not re-calculating the same data twice. 
 
Just wondering if if there is a function to get the same result without the need to join them like this (for future reference). 

manib0907 61 posts Joined 04/15
25 Apr 2015

Johnsnow,
There is no table B. You have to join the same table. If your tablename is "SALES" then the join would be
SELECT
STATE,
WEEK,
YEAR,
SUM(A.SALES) OVER(PARTITION BY A.YRWK) AS TYSALES
SUM(B.SALES) OVER(PARTITION BY B.YRWK) AS LYSALES
FROM SALES A
INNER JOIN SALES B
ON A.LYWK=B.YRWK
QUALIFY ROW_NUMBER() OVER(PARTITION BY STATE ORDER BY STORE)=1;

Cheers,
Mani

You must sign in to leave a comment.