Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL queries for counter web visits per day, month, year and totals
Let us understand how to form the query to find the number of web visits per day, per month, per year, and the total in MySQL:
Note: We assume we have created a database named ‘DBNAME’ and a table named ‘tableName’.
Let us see the MySQL query which can be used to get the web visits per day, month, year and totals −
Query
SELECT COUNT(DISTINCT ip) FROM tableName WHERE create_at >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH AND create_at < LAST_DAY(NOW()) + INTERVAL 1 DAY
The above query searches through a range of DATETIME values by beginning from the present month and searching upto, and until, but without including the beginning of the next month.
Next, a compound covering index on the (create_at, ip) is created. The above query will give the count of the web visits per day, per month, per year.
MySQL can scan the index range which it requires.
Note: The above query works fine for TIMESTAMP data too.
427 Views
