MySQL Query to return data grouped by first day of the week-CodePudding

i have a table of subscriptions with columns id, date, subscription_id. Now, i want a query to return the total subscriptions (count) grouped weekly by the first day of the week (sunday) like so; Subscriptions table;

id	client	sub_id	date
1	john	100	08/05/2022
2	james	101	09/05/2022
3	jane	102	09/05/2022
4	anne	103	11/05/2022
5	mike	104	17/05/2022
6	james	105	19/05/2022
7	andy	106	22/05/2022
8	sol	107	23/05/2022
9	john	108	23/05/2022
10	phil	109	24/05/2022
11	gary	110	25/05/2022
12	dave	111	26/05/2022

sample result;

Week Starting	Total
08/05/2022	4
15/05/2022	3
22/05/2022	6

I have tried this;

SELECT WEEK(date) AS Week, COUNT(*) AS Total FROM subscriptions GROUP BY Week;

but i get the week number instead of first day of the week as indicated in my sample result.

How can i achieve what i indicated in the sample result?

CodePudding user response：

Here is my proposal, let say the table is name subscription

select count(id) `Total`, date_format(created_at, '%Y-%m-%d') `Week Starting`
from subscription where date_format(created_at, '%a') = 'Sun'
group by `Week Starting`

Hope it can help.

CodePudding user response：

You can try

select DATE_FORMAT(created_at,'%Y%U') as year_week,ANY_VALUE(STR_TO_DATE(CONCAT(DATE_FORMAT(created_at,'%Y%U') ,"0"),'%Y%U%w')) as first_day_of_week,count(*) from yourtable group by year_week;