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

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

Financial Year from April to March

If the financial years starts from 1st April and ends at 31st March then 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.

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 perticular 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
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`
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` 
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
We used math functions Average , Sum in our query. We can develop similar query to conside Sep to Aug as financial year
Your Rating




Google+

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








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