Group by in Date field

Date group by 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.

Creating Yearly,monthly,year–month, part of month and quarter reports using group by in date column

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
  • Financial Year with Quarter: FY2015-16 - Q1, FY2016-17 - Q2
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

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

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

Year Month and half of Month

We will display in combination of year-month ( as explained above ) along with part of the month data in 15 days bundle like 1-15th and from 16th to end of the month.

We used IF condition here . We will add the output of IF to CONCAT.
SELECT 
CONCAT( YEAR( payment_dt ) , '-', MONTHNAME( payment_dt ) , '-', 
	IF( DAY( payment_dt ) <=15, 'M-1', 'M-2' ) ) AS ym,
COUNT( * ) AS Nos, SUM( amount ) AS total, AVG( amount ) AS average
FROM `plus2_bills`
GROUP BY ym
ymNostotalaverage
2015-April-M-1154385438.0000
2015-April-M-2157345734.0000
2015-March-M-12122086104.0000
2015-October-M-1145364536.0000
2015-October-M-2137863786.0000
2015-September-M-12120626031.0000
2016-February-M-1167546754.0000
---------------
There are more records in above table

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
Query using CASE WHEN THEN END matching
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 from 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

Financial Year in YYYY-YY format

SELECT 
CASE WHEN MONTH(payment_dt)>=4 
THEN CONCAT(YEAR(payment_dt), '-',DATE_FORMAT(payment_dt,'%y')+1)
ELSE concat(YEAR(payment_dt)-1,'-',DATE_FORMAT(payment_dt,'%y')) 
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-152122086104.0000
2015-168459685746.0000
2016-178408855110.6250
2017-188406125076.5000

Financial Year and Quarter

Each financial year is divided in 4 quarters. We can display records based on financial year and then on each quarter. Here is the query
SELECT CONCAT(YEAR(payment_dt), '-Q',quarter(payment_dt)) as qt_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average 
FROM `plus2_bills` GROUP BY qt_year
QUARTER: to get data on different Quarters of Financial years
Exercise : Exercise on Date query to generate reports Click Track system using Group by in Date column
SQL Date References Records between two dates using DATE_SUB
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    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

    13-09-2019

    I am using below query

    SELECT CONCAT( YEAR( payment_dt ) , '-', MONTHNAME( payment_dt ) , '-', IF( DAY( payment_dt ) <=15, 'M-1', 'M-2' ) ) AS ym,
    COUNT( * ) AS Nos, SUM( amount ) AS total, AVG( amount ) AS average
    FROM `plus2_bills`
    GROUP BY ym


    its working fine. Now i want to show data 1 to 15 if date is 16 and 16-30/31 if date is first of next month using query


    for example :

    List like :

    Date

    2019-August-M-1


    2019-August-M-2


    2019-September-M-1

    so i want 2019-September-M-1 comes on 16 sep.

    27-09-2019

    Solution to this is added in the content of this article

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer