Creating Yearly,monthly,year–month, part of month and quarter reports using group by in date column

From a date column we can create reports by using GROUP BY command. First we will try to create a report of total number of records in a year.
SELECT YEAR(payment_dt) , COUNT(*) FROM plus2_bills GROUP BY YEAR(payment_dt)
We can add total amount and average amount to this query.
SELECT YEAR(payment_dt) , COUNT(*) as Nos, SUM(amount) as total, AVG(amount) as average FROM plus2_bills GROUP BY YEAR(payment_dt)
WE can get report month wise also

SELECT MONTHNAME(payment_dt) as m, COUNT(*) as Nos, SUM(amount) as total, AVG(amount) as average FROM plus2_bills GROUP BY m
Year Month
We can combine year and month and then group them by using CONCAT function which joins strings.
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

By using if condition check we can find out part of the month. Upto 15th of the month we will call M-1 and other dates we will call M-2. We will join Year Month and part of the month as M-1 or M-2 and develop this query.

SELECT CONCAT( YEAR( payment_dt ) , '-', MONTHNAME( payment_dt ) , '-', IF( DAY( payment_dt ) less than =15, 'M-1', 'M-2' ) ) AS ym,COUNT( * ) AS Nos, SUM( amount ) AS total, AVG( amount ) AS average FROM `plus2_bills` GROUP BY ym

Using Financial Year
SELECT
CASE WHEN MONTH(payment_dt)greater than =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

We can create report based on Financial year starting 1st Oct to 30th Sep.

SELECT
CASE WHEN MONTH(payment_dt)greater than =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

Considering financial year from 1st July to 30th June
SELECT
CASE WHEN MONTH(payment_dt)greate than =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)greate than =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
We can combine financial year and quarter and generate report based on year and quarter.