Breadbaker
asked on
Average number of records for past 3 fiscal years grouped by fiscal month
Hi Experts:
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:
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
84 01
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.
Any ideas?
Thanks!
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,
od.fiscal_month,
od.fiscal_year
from orders o
INNER JOIN order_date od ON o.datesubmitted = od.caldate
where od.fiscalyear in (2013,2012,2011)
and o.order_type='Y'
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
84 01
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.
Any ideas?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a bunch.. I was very close, but this cleared it up!
ID: 39765506
is illegal syntax (no group by) and the analytic will produce the incorrect results
you need to maintain the year within the groups/partitions
is illegal syntax (no group by) and the analytic will produce the incorrect results
you need to maintain the year within the groups/partitions
(select count(*) over (partition by od.fiscal_month order by od.fiscal_year) cnt, fiscal_month fm
from orders o inner join order_date od on o.datesubmitted = od.caldate
where od.fiscal_year between 2011 and 2013
and o.order_type = 'Y')