2

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 |
+------+----------+-------+
  • Is just for a single report, but I want to show it the more "human - readable" possible – tachomi Feb 16 '17 at 22:04
2

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; 
  • Glad to help!!! – wogsland Feb 17 '17 at 4:17

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.