I have tried looking at some similar examples like group by date range and weekdays etc but I couldnt fix it on my query.
as per my sample data screenshot, I need to only return
sum(salesamount)/sum(salescount) for week 1
sum(salesamount)/sum(salescount) for week 2.
wednesday - sunday
select salesstartdate, date_add(salesstartdate, interval 5 day) as gdate,
salesamount, salescount, sum(salesamount)/sum(salescount) as ATV
group by gdate;
Week 1 15.34173913
Week 2 15.80365088
You will need to use a subquery here. In order to first group your result set properly and then execute aggregation on it:
SELECT concat('WEEK', ' ', weekno) as `Week #`, MIN(salesstartdate) as startDate, MAX(salesstartdate) as endDate, sum(salesamount)/sum(salescount) as ATV FROM ( SELECT salesstartdate, salesamount, salescount, WEEKOFYEAR(salesstartdate) as weekno -- get the week number of the current year FROM weekno WHERE WEEKDAY(salesstartdate) BETWEEN 2 AND 6 -- get index of week day ) as weeks GROUP BY weekno
I have used 2 MySQL functions here:
WEEK 23 | 2016-06-08 | 2016-06-12 | 15.8040 WEEK 24 | 2016-06-16 | 2016-06-19 | 15.9323