USE my_tutorial;
SELECT QUARTER('2018-12-13');
SELECT QUARTER(CURDATE());

SELECT QUARTER(payment_dt), COUNT(*) FROM plus2_bills GROUP BY QUARTER(payment_dt);

SELECT QUARTER(payment_dt), COUNT(*) FROM plus2_bills WHERE YEAR(payment_dt)=2016 GROUP BY QUARTER(payment_dt);

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;

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;

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;

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;

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;