SQL weekly aggregations

Question

I am a SQL beginner. I need some help in designing an effificent SQL query for the below use case.

Table 1: 
1. Id
2. Cost
3. Price 
4. Date

Table 2:
1. Week Id
2. Week start_date
3. Week end_date

Now I want to find the average cost and price for a given item id aggregated over a week along with the week's start date. Something like this

select a.id, avg(a.cost), avg(a.price), b.start_date
from table 1 a
......
Group by (a.id, b.week_id)

Which database? Each platform offers different aggregate/analytic functions — Cody Caughlan, Mar 07 '14 at 22:40
Side note, BAD IDEA to use reserve keywords like `Date` as table column name. — Shiva, Mar 07 '14 at 22:43
if you group by Week_id you don't show in field list start_date — Joe Taras, Mar 07 '14 at 22:45
Sorry - Its not Date in the actual table. Added it here just for the sake of clarity. — ASingh, Mar 07 '14 at 22:45

score 1 · Accepted Answer · answered Mar 07 '14 at 22:43

1

If you have two tables - item and Week it should be something like this:

SELECT item.Id, AVG(Cost), AVG(Price), Week.Id, MAX(Week.start_date) 
FROM item JOIN Week
ON Date >= start_date AND Date <= end_date
GROUP BY item.Id, Week.Id

answered Mar 07 '14 at 22:43

Avt

16,927
4
52
72

Thanks Avt. But why Max on start_date? – ASingh Mar 07 '14 at 22:46
Each output field should be in the GROUP BY list or be an aggregate function. You can remove `MAX` and use `GROUP BY item.Id, Week.Id, start_date` instead. – Avt Mar 07 '14 at 22:49
Here http://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql it is written that in mySql it is not necessary, but in standard SQL it is. `In standard SQL (but not MySQL), when you use GROUP BY, you must list all the result columns that are not aggregates in the GROUP BY clause` – Avt Mar 07 '14 at 22:52

SQL weekly aggregations

1 Answers1