I need to count a certain type of order for the past 3 fiscal years breaking the data out by fiscal month. So the first number would be the average the count of records for Jan 2013, Jan 2012, Jan 2011, and so on. The database has a table called ORDERS and one called ORDER_DATE
Here is what I came up with for the full set of data:
select count(*) as myCount,
from orders o
INNER JOIN order_date od ON o.datesubmitted = od.caldate
where od.fiscalyear in (2013,2012,2011)
group by od.fiscal_year,od.fiscal_month
order by od.fiscal_month
I get this:
cnt fm fy
72 01 2011
84 01 2012
96 01 2013
72 02 2011
85 02 2012
90 02 2013
and so on for each fiscal month for each of the three fiscal years.
What I really want is the average for each month like this:
avg fm (72+94+96)/3
82.3 02 (72+85+90)/3
and so on.
I can get the average of ALL, but I want it broken out by fiscal month over the past 3 fiscal years.