Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, maxwait.

Asked: March 28, 2017 - 9:09 am UTC

Last updated: March 28, 2017 - 12:25 pm UTC

Version: Oracle 11

Viewed 1000+ times

You Asked

Good afternoon Tom! I had a difficulty with the query with intermediate totals (group would rollup). You can tell how to apply the having condition to the intermediate results.
having sum(Proc)<7

with query1 as 
(
     select '123' schet, '1111101' INN, 'OrgName' nameorg, 12.12 SummAll, 123.12 Summ1, 4.04 Proc, 'Ranch1' fil from dual
     union
     select '124' , '1111102' , 'OrgName1' , 1.01 , 1.02 , 6.23 , 'Ranch2'  from dual
     union
     select '125' , '1111103' , 'OrgName3' , 2.02 , 2.02 , 2.02 , 'Ranch1'  from dual
     union
     select '126' , '1111103' , 'OrgName33' , 3.03 , 3.03 , 3.03 , 'Ranch3'  from dual
     union
     select '127' , '1111105' , 'OrgName11' , 4.04 , 4.04 , 4.24 , 'Ranch1'  from dual
     union
     select '128' , '1111105' , 'OrgName12'  , 5.05 , 5.05 , 5.25 , 'Ranch2'  from dual
     union
     select '129' , '1111107' , 'OrgName111' , 6.06 , 6.06 , 6.26 , 'Ranch1'  from dual
     union
     select '130' , '1111107' , 'OrgName122' , 7.07 , 7.07 , 7.27 , 'Ranch2'  from dual
     union
     select '131' , '1111107' , 'OrgName133' , 8.08 , 8.08 , 8.28 , 'Ranch3'  from dual

)
select * 
from (
    select INN, 
           decode(grouping_id(INN,nameorg),1,'Всего по INN '||INN||':',3,'Итого:',nameorg) nameorg, 
           schet, 
           fil,
           sum(SummAll) SummAll, 
           sum(Summ1) Summ1, 
           sum(Proc) Proc,
           decode(grouping_id(INN,nameorg),3,1,0) seq_1,
           decode(grouping_id(INN,nameorg),1,1,0) seq_2
    from query1
    group by rollup(INN, (nameorg, schet, fil))
-- having Proc < 7
    order by seq_1,INN,seq_2,nameorg
)


I need result
1 1111101 OrgName   123   Ranch1    12.12    123.12   12.04   0 0
2 1111101 Total for INN 1111101:    12.12    123.12   12.04 0 1
3 1111102 OrgName1  124   Ranch2    1.01     1.02     6.23 0 0
4 1111102 Total for INN 1111102:    1.01     1.02     6.23 0 1
5 1111103 OrgName3  125   Ranch1    2.02     2.02     2.02 0 0
6 1111103 OrgName33 126   Ranch3    3.03     3.03     3.03 0 0
7 1111103 Total for TIN 1111103:    5,05     5,05     5,05 0 1

and Chris said...

So you want to show all the rows where the subtotal is less than some value, plus the subtotal rows that are less than that value?

If so, you can do this with analytics!

On the results if your rollup, get the sum for each "INN" and current grouping level. You can do this by adding add grouping_id across the grouped columns:

grouping_id (  inn, nameorg, schet, fil )


Then get the total by inn and grouping level using:

sum(proc) over (partition by inn, grp)


You can then filter the results of this sum to find those where this sum < 7:

with query1 as
  (select '123' schet, '1111101' inn, 'OrgName' nameorg,
    12.12 summall, 123.12 summ1, 4.04 proc,
    'Ranch1' fil
  from dual  union
  select '124' , '1111102' , 'OrgName1' ,
    1.01 , 1.02 , 6.23 ,
    'Ranch2'
  from dual  union
  select '125' , '1111103' , 'OrgName3' ,
    2.02 , 2.02 , 2.02 ,
    'Ranch1'
  from dual  union
  select '126' , '1111103' , 'OrgName33' ,
    3.03 , 3.03 , 3.03 ,
    'Ranch3'
  from dual  union
  select '127' , '1111105' , 'OrgName11' ,
    4.04 , 4.04 , 4.24 ,
    'Ranch1'
  from dual  union
  select '128' , '1111105' , 'OrgName12' ,
    5.05 , 5.05 , 5.25 ,
    'Ranch2'
  from dual  union
  select '129' , '1111107' , 'OrgName111' ,
    6.06 , 6.06 , 6.26 ,
    'Ranch1'
  from dual  union
  select '130' , '1111107' , 'OrgName122' ,
    7.07 , 7.07 , 7.27 ,
    'Ranch2'
  from dual  union
  select '131' , '1111107' , 'OrgName133' ,
    8.08 , 8.08 , 8.28 ,
    'Ranch3'
  from dual
  ), grps as (
select *
from
  (select inn, 
    decode ( grouping_id ( inn,nameorg ) ,1,'Всего по INN '||inn ||':',3,'Итого:',nameorg ) nameorg, 
    schet, fil,
    sum ( summall ) summall, 
    sum ( summ1 ) summ1, 
    sum ( proc ) proc,
    decode(grouping_id(INN,nameorg),3,1,0) seq_1,
    decode(grouping_id(INN,nameorg),1,1,0) seq_2,
    grouping_id (  inn, nameorg, schet, fil ) grp
  from query1
  group by rollup ( inn, ( nameorg, schet, fil ) )
  )
), tots as (
  select grps.*, sum(proc) over (partition by inn, grp) sm from grps
)
  select * from tots
  where  sm < 7
  order by inn nulls last,grp,nameorg;

INN      NAMEORG                SCHET  FIL     SUMMALL  SUMM1   PROC  SEQ_1  SEQ_2  GRP  SM    
1111101  OrgName                123    Ranch1  12.12    123.12  4.04  0      0      0    4.04  
1111101  ????? ?? INN 1111101:                 12.12    123.12  4.04  0      1      7    4.04  
1111102  OrgName1               124    Ranch2  1.01     1.02    6.23  0      0      0    6.23  
1111102  ????? ?? INN 1111102:                 1.01     1.02    6.23  0      1      7    6.23  
1111103  OrgName3               125    Ranch1  2.02     2.02    2.02  0      0      0    5.05  
1111103  OrgName33              126    Ranch3  3.03     3.03    3.03  0      0      0    5.05  
1111103  ????? ?? INN 1111103:                 5.05     5.05    5.05  0      1      7    5.05 

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Group by rollup

Maxwait, March 28, 2017 - 11:54 am UTC

Thank you so much!
Connor McDonald
March 28, 2017 - 12:25 pm UTC

glad we could help

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.