Question 112762 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Group data for SQL

https://i.stack.imgur.com/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!

Tags:

2

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search