Query to calculate the average of each quarter
P粉521697419
P粉521697419 2024-01-29 11:58:00
0
1
435

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

P粉521697419
P粉521697419

reply all(1)
P粉788765679

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)
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template