ll2016 - 9 months ago 39

MySQL Question

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`

and

`sum(salesamount)/sum(salescount) for week 2.`

Each of the week contain 5 days (in this example is

`wednesday - sunday`

`select salesstartdate, date_add(salesstartdate, interval 5 day) as gdate,`

salesamount, salescount, sum(salesamount)/sum(salescount) as ATV

from testing

group by gdate;

`Week 1 15.34173913`

Week 2 15.80365088

Calculation to get

`week 1`

`(3507.1+3639.97+5258.77+8417.04+5994.48)/(285+273+344+478+368)`

Calculation to get

`week 2`

Answer

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:

Output:

```
WEEK 23 | 2016-06-08 | 2016-06-12 | 15.8040
WEEK 24 | 2016-06-16 | 2016-06-19 | 15.9323
```