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.

Bill payment system

Table name plus2_bills
bill_nopayment_dtamount
345212015-03-043412
567322015-03-098796
67598 2015-04-115438
Listing of data based on following conditions

  • Calendar Year: 2015,2016, 2017 ...
  • Year- Month : 2015-Apr, 2016-Jan
  • Month wise ( in all years ): Jan , Feb
  • Financial Year: FY2015-16, FY2016-17
We will include all listing with number of bills paid, total payment and average value of bill

Calendar Year

SELECT YEAR(payment_dt),COUNT(*) FROM `plus2_bills` GROUP BY YEAR(payment_dt)
YEAR(payment_dt)COUNT(*)
20158
20168
20178
20182
With AVG, SUM commands
SELECT YEAR(payment_dt),COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average FROM `plus2_bills` GROUP BY YEAR(payment_dt)
YEAR(payment_dt)Nostotalaverage
20158437645470.5000
20168462025775.2500
20178406025075.2500
2018291054552.5000

Year-Month

SELECT CONCAT(YEAR(payment_dt),'-',MONTHNAME(payment_dt)) as ym ,COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average FROM `plus2_bills` GROUP BY ym

CONCAT to join strings

ymNostotalaverage
2015-April2111725586.0000
2015-March2122086104.0000
2015-October283224161.0000
2015-September2120626031.0000
2016-February2144127206.0000
2016-July291034551.5000
2016-June2113995699.5000
2016-October2112885644.0000
2017-August2101865093.0000
2017-December2100025001.0000
2017-January290954547.5000
2017-May2113195659.5000
2018-March291054552.5000

Month wise

SELECT MONTHNAME(payment_dt) as m ,COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average FROM `plus2_bills` GROUP BY m
mNostotalaverage
April2111725586.0000
August2101865093.0000
December2100025001.0000
February2144127206.0000
January290954547.5000
July291034551.5000
June2113995699.5000
March4213135328.2500
May2113195659.5000
October4196104902.5000
September2120626031.0000

Different Financial Year wise

Let us start with financial year from 1st April to March 31st.
SELECT CASE WHEN MONTH(payment_dt)>=4 THEN CONCAT(YEAR(payment_dt), '-',YEAR(payment_dt)+1) ELSE concat(YEAR(payment_dt)-1,'-',YEAR(payment_dt)) END AS financial_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average FROM `plus2_bills` GROUP BY financial_year
financial_yearNostotalaverage
2014-20152122086104.0000
2015-20168459685746.0000
2016-20178408855110.6250
2017-20188406125076.5000
Financial year starting from 1st October to 30th September
SELECT CASE WHEN MONTH(payment_dt)>=10 THEN CONCAT(YEAR(payment_dt), '-',YEAR(payment_dt)+1) ELSE concat(YEAR(payment_dt)-1,'-',YEAR(payment_dt)) END AS financial_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average FROM `plus2_bills` GROUP BY financial_year
Financial year starting fro 1st July to 30th June
SELECT CASE WHEN MONTH(payment_dt)>=7 THEN CONCAT(YEAR(payment_dt), '-',YEAR(payment_dt)+1) ELSE concat(YEAR(payment_dt)-1,'-',YEAR(payment_dt)) END AS financial_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average FROM `plus2_bills` GROUP BY financial_year

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
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

Subscribe to our mailing list

* indicates required
Subscribe to plus2net
Visitors Rating
Your Rating


Google+
Andrew Ames

22-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
smo

30-10-2010

Solution to this is added to the tutorial, thanks.
gani

12-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
Nashirmohammad Marediya

07-05-2014

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

Post Comment This is for short comments only. Use the forum for more discussions.




Subscribe to our mailing list

* indicates required
Subscribe to plus2net




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer