check the OLAP version of AVG
select day_of _calendar,
avg(day_of _calendar) over () as avg_day,
case when day_of _calendar < avg_day then 'less' else 'more' end
where year_of _calendar = 2012
and month_of _year = 6;
I like to quote dieter her
"Whom do you believe?
Your first hand experience when you actuallly tried ... or some braindumps or other dubious sources?"
Try and read the documenation ;->
But some comments on your questions:
1. yes, in () you can add some more options -> read the manual.
2. yes, but beside oder by you can also specify partition by which, again read the manual.
3. try it