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.


Comments

Post a Comment

Popular posts from this blog

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

c# - ErrorThe type or namespace name 'AxWMPLib' could not be found (are you missing a using directive or an assembly reference?) -

-
what possible reference handler axwmplib._wmpocxevents_playstatechangeeventhandler ? when pass event in method, private void axwindowsmediaplayer1_playstatechange(object sender,axwmplib._wmpocxevents_playstatechangeevent e) compiling throws following error: the type or namespace name 'axwmplib' not found (are missing using directive or assembly reference?) if using visual studio make sure have axwmplib listed under references in solution explorer. if have embedded windows media player control in form, should have been added automatically along wmplib . an easy way add these libraries if missing add windows media player control form (an existing 1 do). right-click on toolbox , pick "choose items..." context menu, tick "windows media player" control on "com components" tab , click ok. should have little windows media player icon attached cursor; click on form add it. add axwmplib , wmplib references solution. can delete w...
Read more