CODEWITHSUNDEEP
sql
ASingh
ASingh

Reputation: 485

SQL weekly aggregations

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)

Upvotes: 0

Views: 98

Answers (1)

Avt
Avt

Reputation: 17043

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

Upvotes: 1

Related Questions