Mohsin Mohsin - 4 months ago 4
SQL Question

Get open and closed posts count group by date with 1 week interval?

I need to get Posts count of closed and open posts grouped by date and with 1 week interval. what i'm getting right now is.

enter image description here

what i want is, like this.

enter image description here

sorry for poor explanation.


Please give it a try:

date_posts - INTERVAL WEEKDAY(date_posts) DAY AS date_start,
date_posts + INTERVAL (6 - WEEKDAY(date_posts)) DAY AS date_start,
COUNT(*) totalPosts,
COUNT(CASE WHEN Close_count = 1 THEN 1 END) closeCount,
COUNT(CASE WHEN Open_count = 1 THEN 1 END) openCount
FROM your_table
GROUP BY YEARWEEK(date_posts,1);


YEARWEEK(date) function assumes the start date is Sunday.

YEARWEEK(date,1) function assumes the start date is Monday.

WEEKDAY(date) function returns the index of the date of the corresponding week assuming Monday as the start day of a week. It returns 0 for Monday, 1 for Tuesday ..... and 6 for Sunday.