https://i.stack.imgur.com/0yDsf.png

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:

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

CodePudding user response：

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

fiddle

CodePudding user response：

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