3

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 |
+------+----------+-------+
1
  • Is just for a single report, but I want to show it the more "human - readable" possible
    – tachomi
    Feb 16 '17 at 22:04
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; 
1
  • Glad to help!!!
    – wogsland
    Feb 17 '17 at 4:17
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;
6
  • 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 at 13:12
  • @nbk I'm not sure what you mean. Solution doesn't group per week but it groups per day. That is it doesn't work for the general case where you want to group per week and you have rows with different days withing the same week. It will only work (will give the same result as grouping by week number) only if dates are already grouped by week.
    – realife
    Mar 8 at 14:38
  • ztry the anser given before with datetimes and you will see that it works,
    – nbk
    Mar 8 at 15:04
  • @nbk I use datetimes in my answer too. Anyway here is an example where accepted solution doesn't group by week but by day i.imgur.com/69z8PD9.png
    – realife
    Mar 8 at 16:42
  • you are right it uses the wrong date_:Format letter , but your code deosn-'t work dbfiddle.uk/…
    – nbk
    Mar 8 at 17:21
-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 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.