I'm new to MySQL and need your help to figure out a query so I can calculate the average of 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 means Jan. 1992. Now I run the below query to get the quarter and month:
select year(date) as Year,monthname(date)as Month, quarter(date) as Quarter, sales from USretail92_21 where kind="Men's clothing stores" order by 1
and that gives me this view:
| Year | Month | 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 can I get the average sales per quarter and have an output that looks like this:
| Quarter | Year | AverageSales |
|---|---|---|
| 1 | 1992 | 696 (average for Jan/Feb/March) |
| 2 | 1992 | 841 |
eventually, I want to have a graph with Python to see sales as Y and "Q1_92 to Q4_21" as X axis
CodePudding user response：
You need to use GROUP BY to calculate aggregates 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 alternatively do it all 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)