Group by command in date field to list number of records in day wise

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.

Mục Lục
Video Tutorial on GROUP BY on Date column Query

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

Video Tutorial on GROUP BY on Date column Query