Averaging an Average
I'm trying to average an average and I'm running into a bit of trouble. I want to determine a daily average of sales and then average the daily averages. So, if I have 3 sales for Day 1 ($5, $10 and $15, average sale being $10) and 4 sales for Day 2 ($1, $2, $3 and $4, average sale being $2.50), I want to average those together. Total Sales (Avg: $10 and $2.50).
I made some progress, by using the PARTION BY function, but the problem is that I can't run an average...
SELECT a.month, a.sales, AVG(a.sales) OVER(PARTITION BY a.day ) AS Daily_Sales
FROM schema.profits a
But now I want to be able to Average that Daily_Sales calculation and then group by the month to get my final result. However, when I try to add in average to the daily_sales, I get "ORA-30483: window functions are not allowed here". Any ideas?
Thanks,
Jeff C.