Group by in Date fieldIf 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 systemWe 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 dtWe 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
|▼ Date & Time functions in SQL|
|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|
|Solution to this is added to the tutorial, thanks.|
|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|
|I need like this type of result how can i ? which query i should apply.|
track_id || dt
126 - 129 || 2010-07-07
130 - 155 || 2010-07-08
179 - 181 || 2010-07-09