I have got the mysql table which includes data about users who visited my web service (IP address, date). The one IP address can be logged in my database only once per day.
What is the best way to create the table contains visits from last month? I want something like:
30.10.2016 | 1457
31.10.2016 | 1604
01.11.2016 | 1590
Sorry for English and maybe the very simple question, first time on stack :)
use this sql code to create a table for last month visitor count.
CREATE TABLE last_mon_visit AS SELECT DISTINCT date, COUNT(ip_address) FROM visit WHERE YEAR(date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) GROUP BY date
i would suggest to create view instead of table.