sql - Restrict records returned by view with group by rollup whilst modifying totals -

i need create view has average columns. view accessed month , year , create global (full table scan) , later use different condition such as: month 05 year 2015 / month 04 year 2015

right have created this:

create or replace view view_x_tipology select dt_ref, gg_ref, mm_ref, yyyy_ref, avg(pcr_qt) pcr_tot, avg(ltr_qt) ltr_tot, avg(tbr_qt) tbr_tot, avg(qt_total) day_total   stock_base  group rollup (dt_ref, gg_ref, mm_ref, yyyy_ref) 

my main problem grand total (i don't need sub totals) has 3 total value, , other fields null

dt_ref      gg_ref mm_ref  yyyy_ref   pcr_tot     ltr_tot     tbr_tot     tot  28/05/2015  28     5       2015       118654      9433        19729       147816         28/05/2015  28     5                  118654      9433        19729       147816         28/05/2015  28                        118654      9433        19729       147816         28/05/2015                            118654      9433        19729       147816         29/05/2015  29     5       2015       132132      10022       19687       161841         29/05/2015  29     5                  132132      10022       19687       161841         29/05/2015  29                        132132      10022       19687       161841         29/05/2015                            132132      10022       19687       161841                                               125393      9727,5      19708       154828,5       

for example, query view_x_tipology in way:

select *   view_per_tipology  mm_ref = 5   , yyyy_ref = 2015 

with query, need days in 05/2015 last line average

update

as ben said, cant create view rollup this, i've used simple union calculate average. sure, work months , year.

select dt_ref, gg_ref, mm_ref, yyyy_ref, pcr_qt, ltr_qt, tbr_qt, qt_total   stock_base union select null, null, mm_ref, yyyy_ref, avg(pcr_qt), avg(ltr_qt), avg(tbr_qt), avg(qt_total)       stock_base      group rollup (dt_ref, gg_ref, mm_ref, yyyy_ref) 

the field qt_* on stock_base have sum of day/month/year. in union, use avg function create average.

the simple answer can't... you're attempting query data doesn't exist. if try work around doing following total still apply entire, original, result-set.

select *   view_per_tipology  ( mm_ref = 5 , yyyy_ref = 2015 )     or dt_ref null 

you're going have write query out each time. alternatively, if always want current month, can restrict view contain data.