Mysql: How to retrieve data on weekly basis

Question

I have a data like this:

And now i want to print this data on weekly basis like this

+------------+-----------+
|    weeks   | sum(count)| 
+----------- +-----------+
| week 1     |  2526     | 
| week 2     |  26987    |
+------------+-----------+

This query sum all mimi_count but i want data in above figure format may be it has to with

group by

.I have searched a lot but could't find what i want

SELECT sum(mimie_count) as mimie 
FROM statistics 
WHERE mimiDate >    DATE_SUB(NOW(), INTERVAL 1 WEEK)

Answer 1

SELECT 
WEEK(mimiDate) weeks,
sum(mimie_count) as mimie 
FROM statistics 
WHERE mimiDate >    DATE_SUB(NOW(), INTERVAL 5 WEEK)
GROUP BY WEEK(mimiDate)
ORDER BY mimiDate;

Note: If you want last X weeks sum week wise then use this DATE_SUB(NOW(), INTERVAL X WEEK). Also note that WEEK function assumes the start of the week is Monday

EDIT: If you want the first column like you stated then you need to adopt the following query:

SELECT 
    CONCAT('week ',WEEK(mimiDate)) weeks,
    sum(mimie_count) as mimie 
    FROM statistics 
    WHERE mimiDate >    DATE_SUB(NOW(), INTERVAL 5 WEEK)
    GROUP BY WEEK(mimiDate)
    ORDER BY mimiDate;

For Specific date range search:

SELECT 
    CONCAT('week ',WEEK(mimiDate)) weeks,
    sum(mimie_count) as mimie 
    FROM statistics 
    WHERE mimiDate BETWEEN '2016-01-21' AND ' 2016-03-05'
    GROUP BY WEEK(mimiDate)
    ORDER BY mimiDate;