R.co R.co - 5 months ago 8
MySQL Question

mysql request every week since last 6 months

I'm trying to request my database to get numbers of rows where a specific login appears from last 6 months ordered by weeks.

The output has to be something like this :

enter image description here

My request is like this :

SELECT count(*) FROM table1 where (`EndDate` BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 MONTH) and CURDATE()) AND the_login LIKE 'LOGIN 1';


It shows every rows from last 6 month for now.
Could you complete my request or change it if needed please ?

Answer

You can use YEAR() and WEEK() to extract them from the date, and group by them .

Note that I didn't use only WEEK because its not enough, if you'll perform this before June, weeks can be misunderstood.

Also, don't use LIKE for exact match, use them only for partially comparison.

SELECT YEAR(t.endDate) as m_Year, WEEK(t.endDate) as m_Week, count(*)
FROM table1 t
where t.`EndDate` BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 MONTH) 
                      and CURDATE())
  AND t.the_login = 'LOGIN 1'
GROUP BY m_Year,m_Week
Comments