aggregate function based on multiple rows

Database
Highlighted
Enthusiast

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

Tags (3)
9 REPLIES
Enthusiast

Re: aggregate function based on multiple rows

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.

Enthusiast

Re: aggregate function based on multiple rows

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. 

Junior Contributor

Re: aggregate function based on multiple rows

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

Enthusiast

Re: aggregate function based on multiple rows

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

Enthusiast

Re: aggregate function based on multiple rows

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

Enthusiast

Re: aggregate function based on multiple rows

Manib,

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

Enthusiast

Re: aggregate function based on multiple rows

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
Enthusiast

Re: aggregate function based on multiple rows

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). 

Enthusiast

Re: aggregate function based on multiple rows

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;