CODEWITHSUNDEEP
phpmysqlsqldategroup-by
AndrewMcLagan
AndrewMcLagan

Reputation: 13987

MYSQL: Grouping by week intervals, where greater then Monday

Say we have a posts table that has the columns: id, title, expires_at. We want to show how many posts where not "expired" in each week of every year.

Maybe a simpler way of putting it would be: "a count of posts grouped by weeks of the year where the expires_at date is great then the start of each week"

For example:

-------------------------------------------------
|   Year    |   Week    |   posts_not_expired   |
------------|-----------|-----------------------|
|   2017    |   01      |   22                  |
|   2017    |   02      |   103                 |
|   2017    |   03      |   7                   |
|   ...     |   ...     |   ...                 |
|   2009    |   52      |   63                  |
|-----------|-----------|-----------------------|

What we have so far:

SELECT
    COUNT(id) as posts_not_expired,
    YEAR(expires_at) * 100 as Year,
    YEARWEEK(expires_at) as Week,
FROM posts
GROUP BY Year, Week

Upvotes: 0

Views: 204

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use DAYOFWEEK to count non-expired posts for a given week. (where 1 = Sunday,2=Monday,..7=Saturday)

SELECT
    YEAR(expires_at) as `Year`,
    WEEKOFYEAR(expires_at) as `Week`,
    SUM(DAYOFWEEK(expires_at) > 2) as `posts_not_expired`
FROM posts
GROUP BY YEAR(expires_at), WEEKOFYEAR(expires_at)

Upvotes: 2

Related Questions