SQL PHP HTML ASP JavaScript articles and free scripts to download

Group by in Date field

If can use group by command in our date field to display total number of records for a day. Say we have records of last ten days; we want to display total records of each day of last ten days. Here we can apply group by command on our date field. Along with the group by command we can use count command to count total number of records in each day. We will discuss some SQL examples using some examples.

Click track system

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

Related Tutorial
Group By
Count
Order By
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 (Year( dt)-Month(dt)-Day(dt))

If we want only year wise data then like this.

SELECT count( dt ) AS no, Year(dt) FROM `dt_tb` GROUP BY (Year( dt))

Now for day wise

SELECT count( dt ) AS no, Day(dt) FROM `dt_tb` GROUP BY (Day(dt))

Why not month also SELECT count( dt ) AS no, Month(dt) 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.

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

Google+

Andrew Ames22-10-2010
What if your date column is a datetime column and the logins are at various times of the day? it then groups by the date and time showing numerous rows for each day? cheers

smo30-10-2010
Solution to this is added to the tutorial, thanks.
gani12-10-2011
hi i need to retrive data from table between specific range of date
i am geeting the range from users
like
String fromdate=fyear+"-"+fmonth+"-"+fdate;
String todate=tyear+"-"+tmonth+"-"+tdate;
will u plz send me the query for this
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



Join Our Email List
Email:  
For Email Newsletters you can trust

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer