How to count the visits of the last 7 days in MySQL

user_14415786 0
Viewed 195

I have a sample table that capture user visits

User|Visit|Date--
Mike|01|1/9/2020--
Mike|01|3/9/2020--
Mike|01|10/9/2020--
John|01|12/9/2020--
John|01|20/9/2020--
Carl|01|16/9/2020--
Carl|01|18/9/2020--
Carl|01|20/9/2020

I want to create a MySQL query such that it captures all the fields/rows from example table and create an additional column "last 7Days". In this column, the idea is to sum all visits from the user from the last 7 days.

The outcome I'm looking out it is something like this

Query_Image

window-functions count date mysql sql
2 Answers

you can use window function with range check this out

select t.*, 
  sum(visit) over (partition by user order by `Date` 
    range between interval + 7 day preceding and current row  ) -1 as x
from t

Fiddle

user_11189780

you can do it with ADDDATE() function

$sql = 'SELECT count(Visit) FROM your_table_name WHERE Date BETWEEN ADDDATE(NOW(),-7) AND NOW()'
user_135367860
Related