Query to calculate the average of each quarter

Question

I am new to MySQL and need your help figuring out a query so that I can calculate the average for each quarter. I have a table called USretail92_21 that looks like this (from 1992 to 2021):

date	Sales
1992-01-01	701.0
1992-02-01	658.0
1992-03-01	731.0
1992-04-01	816.0
1992-05-01	856.0
1992-06-01	853.0
1992-07-01	101.0
1992-08-01	558.0
1992-09-01	431.0

Consider the date format 1992-01-01 to represent January 1992. Now I run the following query to get the quarter and month:

Select the year (date) as the year, the month name (date) as the month, the quarter (date) as the quarter, and the sales come from USretail92_21, where kind="men's clothing store" order by 1 This gives me this view:

Year	moon	Quarter	Sales
1992	January	1	701.0
1992	February	1	658.0
1992	March	1	731.0
1992	April	2	816.0
1992	May	2	856.0
1992	June	2	853.0

Now my question to you is how to get the average sales per quarter and get the output as shown below:

Quarter	Year	Average sales
1	1992	696 (January/February/March average)
2	1992	841

Ultimately, I want to draw a chart in Python that treats sales as the Y-axis and "Q1_92 to Q4_21" as the X-axis

Answer 1

You need to use GROUP BY to calculate aggregations like sums and averages.

Working from your example:

WITH SalesPerMonth AS (
  select year(date) as Year,
  monthname(date)as Month, 
  quarter(date) as Quarter, 
  sales from USretail92_21 
  where kind="Men's clothing stores" 
)
SELECT Quarter, Year, AVG(Sales) AS AverageSales
FROM SalesPerMonth 
GROUP BY Quarter, Year

Or complete all operations at once:

select year(date) as Year,
       quarter(date) as Quarter, 
       AVG(sales) AverageSales
from USretail92_21 
where kind="Men's clothing stores"
group by year(date),
         quarter(date)