`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.

`SELECT QUARTER(CURDATE())`

CURDATE() returns todays date
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

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 |

Above query returns records by grouping them in different quarters irrespective of years

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 |

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
```

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

```
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 ```
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 |

```
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
```

```
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 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 ReferencesMONTH()

Subscribe to our YouTube Channel here

This article is written by **plus2net.com** team.
https://www.plus2net.com