Imagine I've got a query similar to this:
SELECT DISTINCT
AVG(x.Order_total) AS Average_order
FROM
(SELECT a.Order_ID, b.Customer, a.Order_date, a.Order_total
FROM Order_table a
JOIN Customer_table b
ON a.Order_ID = b.Order_ID
WHERE a.Store = 'Random_store'
AND a.Order_date BETWEEN 'Jan 01 2017 12:00AM' AND 'Feb 01 2017 12:00AM'
GROUP BY a.Order_ID, b.Customer, a.Order_date, a.Order_total) x
Let's say that this returns an average order value for all transactions executed in January 2017.
And let's say the output would look like this:
| Average_order |
|---|
| $10 000 |
But what if I want to also calculate average order value for all the following months (February 2017, March 2017, April 2017,...September 2021) and display it in one table without the necessity of manually changing the date and running the calculation many times?
The ideal output would be like this (result numbers are just random):
| Month_and_year | Average_order |
|---|---|
| January 2017 | $10 000 |
| February 2017 | $12 000 |
| March 2017 | $11 500 |
...for all the months till September 2021. (The additional column is not essential, but it would be interesting to know how to construct it.)
I am not a programmer at all, but my guess would be utilizing some kind of for cycle (I am familiar with this programming concept) running through all the remaining combinations of months and years, but honestly - I have no clue how I would go about it in SQL.
Or is there some better way how to run these calculations in bulk?
If it's important, I am using Salesforce Object Query Language(SOQL), which doesn't seem to be always perfect, but I am definitely interested in any SQL dialect. The idea is what matters.
Thank you for any kind of advice!
You don't necessarily need a for construct in your query. If you rephrase your question, what you actually want is: a query that will for all the months between startdate and enddate, calculate the average of all the orders made in that month.
Or in other words: SELECT the AVERAGE of the order totals GROUPED BY ORDER_DATE.MONTH and ORDER_DATE.YEAR where the ORDER_DATE is between startdate and enddate.
Also: is your current query "correct"? It seems weird to group by a Order ID when you are selecting from the Order table - I'd assume Order ID is already unique. Or is it actually calculating the average order per customer? I couldn't tell. In the future, showing some sample data from Order_table and Customer_table would be useful :)
Trying my best to tie together code samples from the SOQL docs without being able to actually test it, I think you want something similar to:
SELECT CALENDAR_YEAR(a.Order_date) AS 'Year', CALENDAR_MONTH(a.Order_date) AS 'Month', AVG(a.Order_total) AS 'Average order total'
FROM Order_table a
WHERE a.Store = 'Random_store'
AND a.Order_date BETWEEN 'Jan 01 2017 12:00' AND 'September 01 2021 12:00AM'
GROUP BY CALENDAR_YEAR(a.Order_date), CALENDAR_MONTH(a.Order_date)
ORDER BY 'Year', 'Month'
CALENDAR_MONTH and CALENDAR_YEAR functions available in SOQL: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm
P.S. I made the assumption that Order_table and Customer_table have a 1-to-1 relationship (i.e. 1 Order has 1 Customer)
P.P.S. There's a dedicated StackExchange for Salesforce questions here: https://salesforce.stackexchange.com/