Click track system by using Group by command in date field

Example :
We have one advertisement slot in our website and we want to count number of clicks the advertisement banner has received. Here as the visitor click the ad we will record the ip address, referrer and date of click. Each click will insert all these data and also the current date in date field. For our further analysis we can keep two more fields tack_id and camp_id. We will not discuss more on all these details and focus on SQL part only. The structure of the table with some sample records can be downloaded at the end of this tutorial. To get the number of clicks for each day we will use like this .

SELECT COUNT(dt) AS no, dt FROM `click_track` GROUP BY dt

We can add order by command to display records starting from highest day ( or current date ) to back by using ORDER BY clause. Here it is

SELECT COUNT(dt) AS no, dt FROM `click_track` 
GROUP BY dt ORDER BY dt DESC

Finally you will get an output like this.

2010-07-09 (3)
	
2010-07-08 (7)
	
2010-07-07 (4)

Group by command with datetime field.

If you have a datetime field where along with date, time is also stored then applying group by command will not return total records of the day. We can't use group by command like above. The group by command has to be applied for Year , month and day part. Like this

SELECT COUNT(  dt ) AS no, dt FROM `click_track` 
	GROUP BY DATE(dt)

If we want only year wise data then like this.

SELECT YEAR(dt) AS year_no, COUNT(*) AS no FROM dt_tb GROUP BY YEAR(dt)

Now for day wise

SELECT DAY(dt) AS day_no, COUNT(*) AS no FROM dt_tb GROUP BY DAY(dt)

Why not month also

SELECT MONTH(dt) AS month_no, COUNT(*) AS no FROM dt_tb GROUP BY MONTH(dt)

Try yourself

If you have a login system where different users login to your discussion forum or to a member area of your site then list out numbers of login in last fifteen days.

After verification of your login details, store a record in your click track system with date, userid and ip address. Then apply group by command and list out number of logins for day.

Total Logins at different Hours of the day

If you are storing date and time in a column ( timestamp ) then you can generate report based on number of logins in different hours of the day.

SELECT HOUR(tm) AS hour_no, COUNT(event_id) AS events FROM dt_table_tm GROUP BY HOUR(tm) ORDER BY hour_no

Here column tm stores timestamp of the event. You can read more on this at Query using DATE_SUB in time

Download the zip file. This file contains the sql dump of the sample table and the track.php file to use the sql commands and display the records using PHP
Group By Date column Records between two dates using DATE_SUB

All Date & Time Functions GROUP BY date Recent-date queries

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Click Track system

Group by command with datetime field.

Try yourself

Total Logins at different Hours of the day

Subhendu Mohapatra