MySQL Quarter to get data from different monthly 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(*)
1	8
2	6
3	6
4	6

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(*)
1	2
2	2
3	2
4	2

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

1^st or Q1 is from 1^st April to 30^th of June.
2^nd Quarter is from July 1^st to Sep 30^th.
3^rd Quarter is from Oct 1^st to Dec 31^st.
4^th Quarter is from 1^st Jan to 31^st 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_year	Nos	total	average
2014-Q4	2	12208	6104.0000
2015-Q1	2	11172	5586.0000
2015-Q2	2	12062	6031.0000
2015-Q3	2	8322	4161.0000
2015-Q4	2	14412	7206.0000
2016-Q1	2	11399	5699.5000
2016-Q2	2	9103	4551.5000
2016-Q3	2	11288	5644.0000
2016-Q4	2	9095	4547.5000
2017-Q1	2	11319	5659.5000
2017-Q2	2	10186	5093.0000
2017-Q3	2	10002	5001.0000
2017-Q4	2	9105	4552.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_year	Nos	total	average
2014-15-Q4	2	12208	6104.0000
2015-16-Q1	2	11172	5586.0000
2015-16-Q2	2	12062	6031.0000
2015-16-Q3	2	8322	4161.0000
2015-16-Q4	2	14412	7206.0000
2016-17-Q1	2	11399	5699.5000
2016-17-Q2	2	9103	4551.5000
2016-17-Q3	2	11288	5644.0000
2016-17-Q4	2	9095	4547.5000
2017-18-Q1	2	11319	5659.5000
2017-18-Q2	2	10186	5093.0000
2017-18-Q3	2	10002	5001.0000
2017-18-Q4	2	9105	4552.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()

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: