Average Monthly Sales by Year

[machumpion]

Greetings y'all,

I have an Order table featuring different store names (StoreName) as text, sales (Sales) as currency, and order date (Order) as Date (yyyy-mm)

I would like to query the average monthly revenue for each store by year.

In Query Design, I add the fields StoreName, OrderDate, and Sales, and set the Total for Sales to "Avg"

Unfortunately, the query only returned the average order amount for every month by store.

How can I query it so that it returns one monthly average sales for each store by year?

Thanks!

 

[CJ_London]

remove the orderdate field. If this does not fix it, post the sql to your query

 

[machumpion]

Hi,

Upon more inspection, it's not actually returning quite the right numbers. The dataset can contain many orders in a given month, it seems the query is averaging the sales across the orders, even if they are in the same month.

I tried to divide the total sales by the count of number of months but it's not quite working. Here's what I have so far:

SELECT Sum(Orders.Revenue)/Count(OrderMonth) AS AverageSales, Orders.Store, Stores.StateProvince
FROM Stores INNER JOIN Orders ON Stores.StoreName = Orders.Store
GROUP BY Orders.Store, Stores.StateProvince
HAVING (((Stores.StateProvince)="ON"));

The fields don't quite match what I stated in my earlier post, I tried to simplify the database as much as possible.

 

[CJ_London]

can you expand on

it's not quite working

 

[machumpion]

can you expand on

I would like to query the average monthly revenue each year for a list of stores. My Orders table has the fields Store, Revenue, Order Month (yyyy-mm).

I would like to see something like:

Store AverageRevenuePerMonth Year
1 $20 2015
1 $25 2016
2 $30 2015
2 $35 2016

My Orders table can have multiple orders in the same month though:

OrderMonth Revenue Store
2016-05 $100 1
2016-05 $200 1

Assuming Store 1 only ordered in 2016-05, my query currently returns $150 for average monthly revenue (100+200)/2records , even though it should be $25 because Store 1 only ordered in May, for 2 orders, and not in any other month (i.e. (100+300)/12months). Do you know how to get the correct output? Thanks in advance.

 

[CJ_London]

even though it should be $25 because Store 1 only ordered in May, for 2 orders, and not in any other month (i.e. (100+300)/12months).

sounds like you want the average sales per year divided by 12 months

something like

SELECT Orders.Store, Stores.StateProvince, left(ordermonth,4) as OrderYear,Sum(Orders.Revenue)/12 AS AverageSales
FROM Stores INNER JOIN Orders ON Stores.StoreName = Orders.Store
WHERE Stores.StateProvince="ON"
 GROUP BY Orders.Store, Stores.StateProvince,left(ordermonth,4)