Group by in Date field

Bill payment system

• Calendar Year: 2015,2016, 2017 ...
• Year- Month : 2015-Apr, 2016-Jan
• Month wise ( in all years ): Jan , Feb
• Financial Year: FY2015-16, FY2016-17
• Financial Year with Quarter: FY2015-16 - Q1, FY2016-17 - Q2

Calendar Year

SELECT YEAR(payment_dt),COUNT(*) FROM `plus2_bills`
   GROUP BY YEAR(payment_dt)

SELECT YEAR(payment_dt),COUNT(*) as Nos,SUM(amount) as total,
  AVG(amount) as average FROM `plus2_bills` GROUP BY YEAR(payment_dt)

Month wise

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

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

CONCAT to join strings

Year Month and half of Month

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

Different Financial Year wise

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

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

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

financial_year	Nos	total	average
2014-15	2	12208	6104.0000
2015-16	8	45968	5746.0000
2016-17	8	40885	5110.6250
2017-18	8	40612	5076.5000

Financial Year and Quarter

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

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

← Subscribe to our YouTube Channel here