Grouping Data to Create a View
Posted on 1998-12-21
I have a table that has sales data for different locations, one record per location for each day:
location_num date amount1 amount2
1 12/7/98 100.00 10.00
1 12/8/98 150.00 5.00
1 12/13/98 100.00 10.00
2 12/7/98 50.00 5.00
2 12/13/98 120.00 12.00
I want to create a view that sums the amounts for each week for each location:
location_num week_ending amount1 amount2
1 12/13/98 700.00 80.00
2 12/13/98 650.00 25.00
I know how to create a select statement when I have the beginning and ending date for the week:
select location_num, sum(amount1), sum(amount2) from xyz where date between '12/7/98' and '12/13/98'.
However, I want to create a view that will sum up the sales for each week without hardcoding any dates into it; I just want to specify that a week is Monday - Sunday and have Sunday show as the week ending date.
I'm not sure this is possible in a view. I know that I can create a table, but I don't want to worry about it getting out of sync (which is actually the situation I am in right now). Does anyone have any ideas how to go about this?