I would like to be able to group by year/week a certain dataset and obtain the first date of such week. An example:
| date | name |
|---|---|
| 2021-01-01 | maria |
| 2021-01-01 | maria |
| 2021-01-08 | joao |
| 2021-01-08 | joao |
By grouping by year/week I would get:
| year/week | count |
|---|---|
| 2021-1 | 2 |
| 2021-2 | 2 |
Transforming the dates before to show the first date of the week would be:
| first_date_week | count |
|---|---|
| 2020-12-27 | 2 |
| 2021-01-03 | 2 |
What I have tried was to apply the simple CONTACT(YEAR(date), '-', WEEK(date)) to the group by function and select:
SELECT
COUNT(date) as count
CONCAT(YEAR(date), '-', WEEK(date))
FROM foo
GROUP BY CONCAT(YEAR(date), '-', WEEK(date))
How can I GROUP BY week and get the first date of that week? I have created a fiddle to help understand the problem: http://sqlfiddle.com/#!9/7b407b/5
