0

i have a table which has sales at day level

sales_day

loc_id  day_id      sales
124     2013-01-01  100
124     2013-01-02  120
124     2013-01-03  140
124     2013-01-04  160
124     2013-01-05  180
124     2013-01-06  200
124     2013-01-07  220

there is weekly table which is the aggregate of all the days loc_id week_id sales 123 201401 1120

Now i need all of the above in table as below 

loc_id  day_id      sales  week_sales
124     2013-01-01  100   1120
124     2013-01-02  120   1120
124     2013-01-03  140   1120
124     2013-01-04  160   1120
124     2013-01-05  180   1120
124     2013-01-06  200   1120
124     2013-01-07  220   1120

there are so many loactions and so many weeks,days. How to get the data exactly without cross join.

mamidipalli satish
  • 1
  • 2

2 Answers2

0

Have you tried this:

select loc_id, day_id, sales, week_sales
from table
cross join (
    select sum(sales) as week_sales from table
) t
Pieter Geerkens
  • 11,775
  • 2
  • 32
  • 52
0

Window analytical function should help you here...

select loc_id, 
       day_id, 
       sales, 
       sum(sales) over(partition by loc_id,date_part('week', day_id)) as week_total_sales
from   <table name>

It will sum the sales by location id and the week of the year to give you the total you are looking for.

In your example, 2013-01-07 was included with the other dates, but it isn't actually part of the same calendar week.

It wasn't clear which DBMS you were referring to. The above is for Netezza. For SQL Server etc try changing date_part('week',day_id) to datepart(ww,day_id).

Scott McGowan
  • 1