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
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