1

Topic: Difference of values in lines on a condition

Kind night, dear participants of a forum!
Help to solve a problem as sorted out, apparently, almost all variants, but decisions and did not find. There is a following data structure. Total lines (where V2, V3 = null) are received by means of window analytic function. RW - a column for sorting of values that total lines were under groups which they sum up.
The task in the following:
To deduce in an additional column (only in total row) a difference between current and previous value (on column V5). For the second total row it will be 52.7 - 80.15, for the first or null or it is possible to receive 0 (80.15 - 80.15). Not total lines should be null.
[img=https://image.ibb.co/jMZ2wG/For_forum.png]
"The live" example laid out here: http://www.sqlfiddle.com/#!4/197a0c/1/0
I will be grateful as for the real help in request change, and for any reasons about it.

2

Re: Difference of values in lines on a condition

ASVG;
It is necessary to you simply GROUPING_sets and lag ()

3

Re: Difference of values in lines on a condition

ASVG wrote:

To deduce in an additional column (only in total row) a difference between current and previous value (on column V5). For the second total row it will be 52.7 - 80.15, for the first or null or it is possible to receive 0 (80.15 - 80.15). Not total lines should be null.

And for 3rd what the result is planned?
For example under the total:

V1 V4 V5
3 - Total 100

4

Re: Difference of values in lines on a condition

ASVG;

SQL> select * from data_src order by 1,2,3,4;
DATA_TYPE DATA_SUB_TYPE P_TXT M_TXT VOL ASVI L_M
---------- ------------- ------ ----- ---------- ---------- ----------
1 0 p_1 m_1 12,05 10 12
1 0 p_1 m_1 17,25 13 9
1 0 p_1 m_2 17,25 13 9
1 1 p_1 m_1 12,05 10 12
1 1 p_2 m_1 10,25 13 9
1 1 p_2 m_2 11,3 13 9
2 0 p_1 m_1 12,15 10 12
2 0 p_1 m_2 18,3 15 8
2 1 p_1 m_2 22,25 20 11
3 1 p_1 m_2 22,25 20 11
10 rows selected.
SQL> with t as (
2 select
3 data_type as v1;
4 data_sub_type as v2;
5 p_txt as v3;
6 m_txt as v4;
7 sum (vol) as v5;
8 sum (asvi) as v6;
9 avg (l_m) as v7;
10 grouping (data_sub_type) gr
11 from data_src
12 group by grouping sets ((data_type, data_sub_type, p_txt, m_txt), (data_type))
13)
14 select t.*
15, decode (gr, 1, lag (v5) over (partition by gr order by v1))-v5 v_sub
16 from t
17 order by 1,2,3,4
18 /
V1 V2 V3 V4 V5 V6 V7 GR V_SUB
---------- ---------- ----- ----- ---------- ---------- ---------- ---------- ----------
1 0 p_1 m_1 29,3 23 10,5 0
1 0 p_1 m_2 17,25 13 9 0
1 1 p_1 m_1 12,05 10 12 0
1 1 p_2 m_1 10,25 13 9 0
1 1 p_2 m_2 11,3 13 9 0
1 80,15 72 10 1
2 0 p_1 m_1 12,15 10 12 0
2 0 p_1 m_2 18,3 15 8 0
2 1 p_1 m_2 22,25 20 11 0
2 52,7 45 10,3333333 1 27,45
3 1 p_1 m_2 22,25 20 11 0
3 22,25 20 11 1 30,45
12 rows selected.
SQL>

....
stax