I am trying to calculate the average points for employees for a period of a week (or calendar month if possible) from a range of records. I have a table: daily_schedule which has records like this
I have tried the query:
SELECT WEEK(date) ,employeeid ,AVG(points) FROM `daily_schedule` GROUP BY WEEK(date), employeeid
But that is calculating the average on jobs rather than the date per employee The result I want to achieve is
Is there any way to achieve the average by week and/or calendar month?
Thank you in advance.#
WEEK(date) function will give you the number of week in the year from that date. And if you want to achieve what you’ve mentioned here, you can simply
group by WEEK(date) and then employeeid. You can execute a CTE in this purpose. Then select the aggregated grouped values from CTE and AVG from
points/days collected from cte.
So you can execute:
with cte as (select employeeid, sum(points) as points, count(distinct date_val) as days, WEEK(DATE_VAL) as week_no from daily_schedule ds group by WEEK(date_val), employeeid) select week_no, employeeid, points, days, points/days as avg from cte;
From the week no, you can identify which week of the year this is. This will give you the expected output.
Output of this query is: