ll2016 ll2016 - 5 months ago 15
MySQL Question

group by date containing certain date only

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
).

My Attempt:

select salesstartdate, date_add(salesstartdate, interval 5 day) as gdate,
salesamount, salescount, sum(salesamount)/sum(salescount) as ATV
from testing
group by gdate;


My desired output is:

Week 1 15.34173913
Week 2 15.80365088


Calculation to get
week 1
is
(3507.1+3639.97+5258.77+8417.04+5994.48)/(285+273+344+478+368)


Calculation to get
week 2
is the same as above except the date would now be from 8 to 12 of June.

sample data

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