QUARTER : Data of different quarters of the year

SELECT QUARTER('2018-03-12')
Output is 1
We will get output as 1 or 2 or 3 or 4 based on the input date.

Present Quarter

SELECT QUARTER(CURDATE())
CURDATE() returns todays date


Grouping financial data based on Financial year Quarters by using the date column

DEMO: Select date from Calendar to get Quarter value
As per MySQL, calendar year is considered as financial year so here is the list of Quarters and its output.

Jan to March First Quarter : Output = 1
April to Jun Second Quarter : Output = 2
July to Sep Third Quarter : Output = 3
Oct to Dec Fourth Quarter : Output = 4

Fiscal Calendar of four quarters.

First Quarter or Quarter one is known as Q1. Similarly second quarter is Q2 , third quarter is Q3 and fourth quarter is Q4 Here are some examples on how to use Quarter function to get data from MySQL table. We used one sample table name plus2_bills for our queries. You can use the same table to test the queries.

Records of each quarter

Using Group by command to get records of each quarter.

SELECT QUARTER(payment_dt) , COUNT(*) from plus2_bills
 GROUP BY QUARTER(payment_dt)
Output is
QUARTER(payment_dt)count(*)
18
26
36
46
SQL Count to get number of records .
Above query returns records by grouping them in different quarters irrespective of years

Records of each quarter of a particular year

WHERE condition to filter records

SELECT QUARTER(payment_dt) , count(*) from plus2_bills  
	WHERE YEAR(payment_dt)='2016' 
	GROUP BY QUARTER(payment_dt)
QUARTER(payment_dt)count(*)
12
22
32
42

Records of each quarter of different Years

SQL CASE WHEN ELSE condition matching.
CONCAT to join string.
Considering Calendar year as financial year ( Jan to Dec )
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

Financial Year from April to March

If the financial years starts from 1st April and ends at 31st March then
  • 1st or Q1 is from 1st April to 30th of June.
  • 2nd Quarter is from July 1st to Sep 30th.
  • 3rd Quarter is from Oct 1st to Dec 31st.
  • 4th Quarter is from 1st Jan to 31st March.
Each record with respective Financial Year and quarter ( April - March )
SELECT DATE_FORMAT( payment_dt,  '%Y-%M-%d' ) AS DATE,
CASE WHEN QUARTER(payment_dt)=1 
	THEN CONCAT(YEAR(payment_dt)-1, '-Q',QUARTER(payment_dt)+3)
	ELSE concat(YEAR(payment_dt),'-Q',QUARTER(payment_dt)-1) 
END AS qt_year
FROM `plus2_bills`
Output List : Each record with matching Financial Year and Quarter
Above query with Financial Year from October to September
SELECT DATE_FORMAT( payment_dt,  '%Y-%M-%d' ) AS DATE, 
CASE WHEN QUARTER( payment_dt ) !=4
 THEN CONCAT( YEAR( payment_dt ) -1,'-Q', QUARTER( payment_dt ) +1 )
 ELSE CONCAT( YEAR( payment_dt ) ,  '-Q', QUARTER( payment_dt )-3 ) 
END AS qt_year 
FROM  `plus2_bills` 
Output List : Each record with matching Financial Year and Quarter

Grouping data along Financial year and Quarter

Now let us considered April to March as financial year. So Jan to March is taken as Q4 of previous year. We will show the Total number of records with Sum of amount and average of amount against each quarter.
SELECT 
CASE WHEN QUARTER(payment_dt)=1 
THEN CONCAT(YEAR(payment_dt)-1, '-Q',QUARTER(payment_dt)+3)
ELSE concat(YEAR(payment_dt),'-Q',QUARTER(payment_dt)-1) 
END AS qt_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average 
FROM `plus2_bills` GROUP BY qt_year
qt_yearNostotalaverage
2014-Q42122086104.0000
2015-Q12111725586.0000
2015-Q22120626031.0000
2015-Q3283224161.0000
2015-Q42144127206.0000
2016-Q12113995699.5000
2016-Q2291034551.5000
2016-Q32112885644.0000
2016-Q4290954547.5000
2017-Q12113195659.5000
2017-Q22101865093.0000
2017-Q32100025001.0000
2017-Q4291054552.5000
From October to September considered as Financial Year
SELECT 
CASE WHEN QUARTER( payment_dt ) !=4
 THEN CONCAT( YEAR( payment_dt ) -1,'-Q', QUARTER( payment_dt ) +1 )
 ELSE CONCAT( YEAR( payment_dt ) ,  '-Q', QUARTER( payment_dt )-3 ) 
END AS qt_year,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average 
FROM `plus2_bills` GROUP BY qt_year
Financial year starting from 1st July to 30th June
SELECT 
CASE WHEN QUARTER( payment_dt ) <=2
 THEN CONCAT( YEAR( payment_dt ) -1,'-Q', QUARTER( payment_dt ) +2 )
 ELSE CONCAT( YEAR( payment_dt ) ,  '-Q', QUARTER( payment_dt )-2 ) 
END AS qt_year,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average 
FROM `plus2_bills` GROUP BY qt_year

YYYY-YY-Q format

Financial Year is from April to March
SELECT 
CASE WHEN QUARTER(payment_dt)=1 
THEN CONCAT(YEAR(payment_dt)-1, '-',DATE_FORMAT(payment_dt,'%y'), '-Q',QUARTER(payment_dt)+3)
ELSE concat(YEAR(payment_dt), '-',DATE_FORMAT(payment_dt,'%y')+1,'-Q',QUARTER(payment_dt)-1) 
END AS qt_year ,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average 
FROM `plus2_bills` GROUP BY qt_year
qt_yearNostotalaverage
2014-15-Q42122086104.0000
2015-16-Q12111725586.0000
2015-16-Q22120626031.0000
2015-16-Q3283224161.0000
2015-16-Q42144127206.0000
2016-17-Q12113995699.5000
2016-17-Q2291034551.5000
2016-17-Q32112885644.0000
2016-17-Q4290954547.5000
2017-18-Q12113195659.5000
2017-18-Q22101865093.0000
2017-18-Q32100025001.0000
2017-18-Q4291054552.5000
We used math functions Average , Sum in our query.
We can develop similar query to consider Sep to Aug as financial year
Group By in Date field
SQL file of date quarter queries
SQL Date References MONTH()
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    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