sql - How do I use the grouping clause in my select statement with grouping sets? -


i learned of using grouping sets clause , believe can re write 1 of old queries. currently, union of 5 different groupings coming cte. understand should able change different hierarchies being grouped grouping sets of rollup, need specify literal @ each level or grouping. can't show actual code, have example below.

select      b.level_one, b.level_two, b.level_three, b.level_four, b.level_five,     case         when (grouping(b.level_five)=1) ''         when ...         when ...         when ...         when ...         when ...     end as'level_type',     sum(b.value) total     base b     ... group grouping sets(     (b.level_one, b.level_two, b.level_three, b.level_four, b.level_five),     (b.level_one, b.level_two, b.level_three, b.level_four),     (b.level_one, b.level_two, b.level_three),     (b.level_one, b.level_two, b.level_four),     (b.level_one, b.level_two),     (b.level_one, b.level_two) )

this general idea of think want go. problem i'm having how specify literal when use either 3rd or 4th grouping set. ideas?

edit:

expected results rows following:

level_one | level_two | level_three | level_four | level_five | level_type | total  'lvl1'    | 'lvl2'    | 'lvl3'      | 'lvl4'     | 'lvl5'     | 'level 5'  | (int) ... 'lvl1'    | 'lvl2'    | 'lvl3'      | 'lvl4'     | null       | 'level 3,4'| (int) ... 'lvl1'    | 'lvl2'    | null       | 'lvl4'     | null       | 'level 4'  | (int) ... 'lvl1'    | 'lvl2'    | 'lvl3'      | null       | null       | 'level 3'  | (int) ... 'lvl1'    | 'lvl2'    | null       | null       | null       | 'level 2'  | (int) ... 'lvl1'    | null      | null        | null       | null       | 'level 1'  | (int) ...

you'll more , better answers if include sql ddl , sample data. code below untested.

to better see what's going on, include column each grouping set.

select      b.level_one, b.level_two, b.level_three, b.level_four, b.level_five,     grouping(level_one)   grp_lvl1,     grouping(level_two)   grp_lvl2,     grouping(level_three) grp_lvl3,     grouping(level_four)  grp_lvl4,     grouping(level_five)  grp_lvl5,     sum(b.value) total     base b     ... group grouping sets(     (b.level_one, b.level_two, b.level_three, b.level_four, b.level_five),     (b.level_one, b.level_two, b.level_three, b.level_four),     (b.level_one, b.level_two, b.level_three),     (b.level_one, b.level_two, b.level_four),     (b.level_one, b.level_two),     (b.level_one, b.level_two) );

i'm not grouping sets wrote ones want. closely @ them.

once examine output of query, you'll able see how structure case...when expression. end looking something this.

(case when (grouping(level_one)   = 1) ,            (grouping(level_two)   = 1) ,            (grouping(level_three) = 1) ,             (grouping(level_four)  = 1) ,             (grouping(level_five)  = 1) 'some string literal here'       when (grouping(level_one)   = 0) ,            (grouping(level_two)   = 1) ,            (grouping(level_three) = 1) ,             (grouping(level_four)  = 1) ,             (grouping(level_five)  = 1) 'some other string literal here'       when ...  end) level_type

Comments

Post a Comment

Popular posts from this blog

linux - xterm copying to CLIPBOARD using copy-selection causes automatic updating of CLIPBOARD upon mouse selection -

i've tried various ways coerce xterm (versions 285 , 292) copy selection clipboard clipboard whenever press ctrl-shift-c. promising way far has been, in ~/.xresources, put this: xterm*vt100.translations: #override \ ctrl shift <keypress> c: copy-selection(clipboard) \n\ ctrl shift <keypress> v: insert-selection(clipboard) ctrl-shift-v works perfectly, copying has nuance... if restart xterm, highlighting text puts things in primary clipboard; expected, proper, default behavior. if hit ctrl-shift-c, copies current selection clipboard clipboard. the bug, however, if highlight text after first time pressed ctrl-shift-c, you'll see highlighting copies both primary and clipboard. cannot figure out how stop xterm updating clipboard upon selection... , doesn't make sense me in first place. i said, @ specific point in time, copy selection clipboard... yet starts updating automatically upon selection after doing once... anyone have workaround...
Read more

c++ - qgraphicsview horizontal scrolling always has a vertical delta -

Image
i've subclassed qgraphicsview custom canvas used in cad application. i've reimplemented qgraphicsview::wheelevent check keyboard modifiers control key and, if control key pressed, zoom. i'm trying implement horizontal scroll when user holds shift , uses wheel. the problem i'm having horizontal scrolling always scrolls 0.279. not huge problem, hugely annoying , points else being wrong. so, here questions: is right way implement horizontal scrolling? if not, is? how eliminate delta of 0.279? thanks in advance. code , sample output below void myview::zoom(int delta) { double factor = pow(1.2, delta/abs(delta)); this->scale(factor, factor); } void myview::scrollhorizontal(int level) { qpointf center = maptoscene(viewport()->rect().center()); qdebug() << "center: " << center.x() << ", " << center.y(); centeron(qpointf(center.x() - level, center.y())); } void myview::wheelevent(qwheeleve...
Read more