There are two relatively similar requests. The second query displays the summary data of the first. Is it possible to somehow optimize them and execute everything in one query? And display the amount at the end of each column?

Main query:

select round(hz,2) HZ,bz,round(h0,1)HO,naim, round(sum(pf),2) PF,round(sum(pt),2) PT, round(sum(pdm),2) PDM,round(sum(pdp),2) PDP,round(sum(pbm),2)PBM,round(sum(pbp),2) PBP, round(sum(tp)/60,2) TP,round(sum(pf)/sum(tp)*60,1)SFP from (select r.hz,r.bz,r.h0,m.naim,r.pf,r.pt,r.pdm,r.pdp,r.pbm ,r.pbp, case when ((r.tz-p.ts)*1440 < 10) and ((r.i_rez3/60) < 10) then (case when rn > 1 then ((r.ts-p.ts)*1440) else ((r.ts-r.tz)*1440) end) when ((r.tz-p.ts)*1440 > 10) and ((r.i_rez3/60) < 10) then (case when rn > 1 then (((r.ts-r.tz)*1440)+10) else((r.ts-r.tz)*1440)end) when ((r.tz-p.ts)*1440 < 10) and ((r.i_rez3/60) > 10) then (case when rn > 1 then ((((r.ts-p.ts)*1440)-(r.i_rez3/60-10)))else((((r.ts-r.tz)*1440)-(r.i_rez3/60-10))) end) when ((r.tz-p.ts)*1440 > 10) and ((r.i_rez3/60) > 10) then (case when rn > 1 then ((((r.ts-r.tz))*1440+10-(r.i_rez3/60-10))) else((((r.ts-r.tz))*1440-(r.i_rez3/60-10)))end) end as tp from zxp.rulon r,zxp.mc m,zxp.rulon p where r.ts between to_date('14.04.16 22:00:00','dd.mm.yy hh24:mi:ss') and to_date('15.04.16 7:00:00','dd.mm.yy hh24:mi:ss')+1 and r.ms = m.ukey and p.ts = ( select max(pp.ts) from zxp.rulon pp where pp.ts <= r.tz and pp.ts > r.tz-1)) group by hz,h0,bz,naim order by hz,h0,bz,naim

Duplicate with amount:

 select 'Итого:',round(sum(pf),2)"Р физ.",round(sum(pt),2)"Р теор.", round(sum(pdm),2)"Р доп. -",round(sum(pdp),2)"Р доп. +",round(sum(pbm),2)"Р бр. -",round(sum(pbp),2)"Р бр. +", round(sum(tp)/60,2)"Длит.",round(sum(pf)/sum(tp)*60,1)"Произв." from (select r.pf,r.pt,r.pdm,r.pdp,r.pbm,r.pbp, case when ((r.tz-p.ts)*1440 < 10) and ((r.i_rez3/60) < 10) then (case when rn > 1 then ((r.ts-p.ts)*1440) else ((r.ts-r.tz)*1440) end) when ((r.tz-p.ts)*1440 > 10) and ((r.i_rez3/60) < 10) then (case when rn > 1 then (((r.ts-r.tz)*1440)+10) else((r.ts-r.tz)*1440)end) when ((r.tz-p.ts)*1440 < 10) and ((r.i_rez3/60) > 10) then (case when rn > 1 then ((((r.ts-p.ts)*1440)-(r.i_rez3/60-10)))else((((r.ts-r.tz)*1440)-(r.i_rez3/60-10))) end) when ((r.tz-p.ts)*1440 > 10) and ((r.i_rez3/60) > 10) then (case when rn > 1 then ((((r.ts-r.tz))*1440+10-(r.i_rez3/60-10))) else((((r.ts-r.tz))*1440-(r.i_rez3/60-10)))end) end as tp from zxp.rulon r,zxp.rulon p where r.ts between to_date('14.04.16 22:00:00','dd.mm.yy hh24:mi:ss') and to_date ('15.04.16 7:00:00','dd.mm.yy hh24:mi:ss') and p.ts =(select max(pp.ts) from zxp.rulon pp where pp.ts <= r.tz and pp.ts > r.tz-1))

    1 answer 1

    Use the ROLLUP function when grouping; it can sum up the subtotals for the specified columns. If we only need a summary of the group by columns, the remaining ones should be enclosed in additional brackets. Because you want to get only a grand total - then all the columns should be enclosed in additional brackets in the rollup. Like that:

     select round(hz,2) HZ,bz,round(h0,1)HO,naim, ..... from ... group by ROLLUP((hz,h0,bz,naim)) --<---- Колонки в двойных скобках для общего итога !!! order by hz,h0,bz,naim

    In the final record, all columns specified for the grouping will be NULL, which can be used to display the words “Total”, etc. Or use the grouping(колонка) function grouping(колонка) which returns 1 - if the given string is a total or 0 - if it is a data string.

    • Thank you, but this is not exactly what I need, apparently I will have to leave two almost identical requests. - Ethernets
    • one
      @Ethernets Why not quite that, you can display the value of grouping (hz) and put all this together in an external query, which, on the basis of this field, rearranges the columns as needed and adds beautiful inscriptions. Or how is your final query different? - Mike
    • by dividing round (sum (tp) / 60,2) "Duration.", round (sum (pf) / sum (tp) * 60,1) "Proc." I understood the meaning I read how to implement in practice - Ethernets
    • one
      @Ethernets I see no difference, in the first query you share the same. And the sum of the divided values ​​is equal to the division of the sum (except for the features of rounding) - Mike
    • Understood, everything works as it should, Thank you very much - Ethernets