Group data for SQL

Question

https://i.sstatic.net/0yDsf.png

year	month	no_of_people	avg
2005	1	Overall	8
2005	2	Overall	5.0
2005	3	Overall	2.7
2005	4	Overall	4.1
2005	5	Overall	6.8
2005	6	Overall	5.2
2005	7	Overall	4.7
2005	8	Overall	4.4
2005	9	Overall	3.8
2005	10	Overall	7
2005	11	Overall	4.9
2005	12	Overall	6.5

My issue lies in essentially calculating the avg of three months (123, 456, etc) and displaying this new value as quarterly average of Q1/2/3/4 (indicating Quarters). Sorry for formatting, but an ideal output would be something like:

year	quarter	no_of_people	avg
2005	Q1	Overall	xxx
2005	Q2	Overall	xxx

Not sure how to even begin with this query and how to group the months into quarters. Any thanks would be very much appreciated!

Answer 1

If you have a date column in that table, you can directly use MySQL QUARTER() function. However, this will return 1-4 instead of Q1-Q4. If you don't have date column, then you can combine year and month column plus a 01 for "day" value, use DATE_FORMAT to make it identifiable as date and use QUARTER() on it:

SELECT `year`,
       QUARTER(DATE_FORMAT(CONCAT_WS('-',`year`,`month`,'01'), '%Y-%m-%d')) 
       AS Quarter,
      sub_housing_type,
      AVG(`avg`) AS Average
FROM mytable
GROUP BY `year`, Quarter, sub_housing_type

Demo fiddle

Answer 2

CREATE TABLE test (
  `year` YEAR,
  `month` TINYINT,
  sub_housing_type VARCHAR(8),
  `avg` DECIMAL(3,1));
INSERT INTO test VALUES
(2005, 1, 'Overall', 90.1),
(2005, 2, 'Overall', 88.9),
(2005, 3, 'Overall', 88.9),
(2005, 4, 'Overall', 90.2),
(2005, 5, 'Overall', 86.8),
(2005, 6, 'Overall', 87),
(2005, 7, 'Overall', 84.8),
(2005, 8, 'Overall', 88.1),
(2005, 9, 'Overall', 88.9),
(2005, 10, 'Overall', 87.5),
(2005, 11, 'Overall', 89.1),
(2005, 12, 'Overall', 83.7);
SELECT * FROM test;

SELECT `year`,
       CONCAT('Q', (`month`+2) DIV 3) `quarter`,
       AVG(`avg`) quarter_avg
FROM test
GROUP BY `year`, `quarter`
ORDER BY `year`, `quarter`;

year	quarter	quarter_avg
2005	Q1	89.30000
2005	Q2	88.00000
2005	Q3	87.26667
2005	Q4	86.76667

fiddle