5

I had this query:

SELECT YEAR(datetime) AS Year, WEEK(datetime) AS Week, COUNT(*) AS total
FROM table
GROUP BY Year, Week;

The result is:

+------+------+-------+
| Year | Week | total |
+------+------+-------+
| 2016 |   49 |    14 |
| 2016 |   50 |    20 |
| 2016 |   51 |    19 |
| 2016 |   52 |    15 |
| 2017 |    1 |    13 |
| 2017 |    2 |    14 |
| 2017 |    3 |    12 |
| 2017 |    4 |    12 |
| 2017 |    5 |     5 

But I want to know how to show the week like "more beautiful", something like:

+------+----------+-------+
| Year |    Week  | total |
+------+----------+-------+
| 2016 |   Dec 05 |    14 |
| 2016 |   Dec 12 |    20 |
| 2016 |   Dec 19 |    19 |
| 2016 |   Dec 26 |    15 |
| 2017 |   Jan 02 |    13 |
| 2017 |   Jan 09 |    14 |
| 2017 |   Jan 16 |    12 |
| 2017 |   Jan 23 |    12 |
| 2017 |   Jan 30 |     5 |
+------+----------+-------+
0

4 Answers 4

6

You can accomplish this with the DATE_FORMAT function,

SELECT YEAR(datetime) AS Year, DATE_FORMAT(datetime, '%b %e') AS Week, COUNT(*) AS total
FROM table
GROUP BY Year, Week; 
0
1

Accepted solution didn't work for me as it doesn't group per week but it can get rows within the same week, example:

year, week, total
2021, Mar 23, 1
2021, Mar 24, 2

Using a subquery seems to be working:

SELECT 
    YEAR(ADDDATE(your_datetime, INTERVAL 1-DAYOFWEEK(your_datetime) DAY)) year,
    DATE_FORMAT(ADDDATE(your_datetime, INTERVAL 1-DAYOFWEEK(your_datetime) DAY), '%b %e') week,
    total
FROM
    (SELECT 
        YEAR(your_datetime) AS year,
            WEEK(your_datetime) AS week,
            COUNT(*) AS total,
            your_datetime
    FROM
        your_table
    GROUP BY `your_datetime`) AS sub;
1
  • datetime in the solution is an actual date time like 2020-01-.01 00:00:00 so date_format works just fine. the tables that you see in the question are only the result. so there is no need for a subquery in the original question
    – nbk
    Mar 7, 2021 at 13:12
1

To group by week and to display the first day of the week you can do as showed. Subtract the Day of the week from your stored date, and then format the date how ever you want. You can then group by your new "date" value and voila !

SELECT DATE_FORMAT(ADDDATE(buy.date_created, INTERVAL -DAYOFWEEK(buy.date_created) DAY),"%Y-%m-%d") as week, COUNT(*) AS total FROM buy GROUP BY week;

Result

+------------+-------+
|week        | total |
+------------+-------+
| 2016-12-05 |    14 |
| 2016-12-12 |    20 |
| 2016-12-19 |    19 |
| 2016-12-26 |    15 |
| 2017-01-02 |    13 |
| 2017-01-09 |    14 |
| 2017-01-16 |    12 |
| 2017-01-23 |    12 |
| 2017-01-30 |     5 |
+------------+-------+

-1
SELECT YEAR(buy.date_created) AS Year, WEEK(buy.date_created) as Week, DATE_FORMAT(buy.date_created, '%b %e') AS WeekString, COUNT(*) AS total
FROM buy
GROUP BY Year, Week
ORDER BY Year, week; 

I Think is this the best proposal.

1
  • This isn't substantially different than the accepted answer - consider providing more detail or waiting until you have enough reputation to upvote the (nearly identical) answer.
    – bbaird
    Aug 23, 2021 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.