# Calculating points from number of days worked in a week/month by each employee

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?

The `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:

week_no employeeid points days avg
26 113877 4.5 2 2.25
26 122396 4.2 2 2.1
26 124514 3.7 1 3.7
27 113877 1.8 1 1.8
27 122396 1.2 1 1.2
27 124514 1.6 1 1.6