I have 3 items - GAO, GAD and GAM and for every item
I need to count average in the bottom of table, below "ALL".
Query:
SELECT NVL(vrz.DATA, 'ALL') AS DAY,
SUM(vrz.SOV_GAO_1_ILE) AS GAO,
SUM(vrz.SOV_GAD_2_ILE) AS GAD,
SUM(vrz.SOV_GAM_3_ILE) AS GAM,
SUM(vrz.SOV_GAO_1_ILE) + SUM(vrz.SOV_GAD_2_ILE) + SUM(vrz.SOV_GAM_3_ILE) AS SUMMARY,
FLOOR((SUM(vrz.SOV_GAO_1_ILE) + SUM(vrz.SOV_GAD_2_ILE) +
SUM(vrz.SOV_GAM_3_ILE))/3) AS AVERAGE
FROM PLC.V_RAPORT_ZDARZEN vrz
GROUP BY ROLLUP (vrz.DATA)
Part of data:
DAY GAO GAD GAM SUMMARY AVERAGE
2017-05-25 1035,0 725,0 0,0 1760,0 586,0
2017-05-26 253,0 785,0 0,0 1038,0 346,0
2017-05-29 1060,0 467,0 0,0 1527,0 509,0
2017-05-30 922,0 905,0 0,0 1827,0 609,0
2017-05-31 1113,0 1343,0 0,0 2456,0 818,0
2017-06-01 1236,0 1057,0 0,0 2293,0 764,0
2017-06-02 1740,0 1182,0 0,0 2922,0 974,0
2017-06-04 22,0 1,0 0,0 23,0 7,0
2017-06-05 1702,0 1534,0 0,0 3236,0 1078,0
2017-06-06 1754,0 1388,0 0,0 3142,0 1047,0
2017-06-07 865,0 1109,0 0,0 1974,0 658,0
2017-06-08 1248,0 1044,0 0,0 2292,0 764,0
2017-06-09 752,0 978,0 0,0 1730,0 576,0
2017-06-12 1403,0 534,0 0,0 1937,0 645,0
2017-06-13 2415,0 850,0 0,0 3265,0 1088,0
2017-06-14 1159,0 458,0 0,0 1617,0 539,0
2017-06-16 361,0 372,0 0,0 733,0 244,0
2017-06-19 1605,0 393,0 0,0 1998,0 666,0
2017-06-20 938,0 694,0 0,0 1632,0 544,0
2017-06-21 2212,0 952,0 0,0 3164,0 1054,0
2017-06-22 1912,0 922,0 0,0 2834,0 944,0
2017-06-23 1030,0 1003,0 0,0 2033,0 677,0
2017-06-26 528,0 111,0 0,0 639,0 213,0
2017-06-27 910,0 485,0 0,0 1395,0 465,0
2017-06-28 708,0 240,0 0,0 948,0 316,0
2017-06-29 1819,0 1122,0 0,0 2941,0 980,0
.......
2017-11-03 809,0 585,0 600,0 1994,0 664,0
2017-11-06 953,0 1474,0 595,0 3022,0 1007,0
2017-11-07 800,0 1250,0 991,0 3041,0 1013,0
2017-11-08 0,0 615,0 1014,0 1629,0 543,0
ALL 131029,0 124262,0 112195,0 367486,0 122495,0
Averages:
GAO GAD GAM 1082,884298 1027,834711 927,231405
Rollup is OK, but in this case I would use CTE and add two summary rows using union all.
with cte as (
select data, count(1) cnt,
sum(sov_gao_1_ile) gao, sum(sov_gad_2_ile) gad, sum(sov_gam_3_ile) gam,
sum(sov_gao_1_ile + sov_gad_2_ile + sov_gam_3_ile) / 3 average
from vrz
group by data)
select to_char(data, 'yyyy-mm-dd') data, gao, gad, gam, gao+gad+gam summary, average
from cte
union all
select 'ALL', sum(gao), sum(gad), sum(gam), sum(gao+gad+gam), sum(average)
from cte
union all
select 'AVG', round(avg(gao)), round(avg(gad)), round(avg(gam)), null, null
from cte
order by 1
As I understand, You want averages from already grouped data, which can differ from average from the whole set. If not then @Wernfried's solution should work for You.
Anyway you can use column cnt to divide sums by counts.
Unions works on aggregated sums, so it should not impact performance. Simplified demo data and output:
with vrz(data, sov_gao_1_ile, sov_gad_2_ile, sov_gam_3_ile) as (
select date '2000-01-21', 111, 112, 114 from dual union all
select date '2000-01-21', 115, 116, 125 from dual union all
select date '2000-01-22', 211, 212, 213 from dual union all
select date '2000-01-22', 215, 216, 223 from dual union all
select date '2000-01-23', 301, 302, 225 from dual),
cte as (
select data, count(1) cnt,
sum(sov_gao_1_ile) gao, sum(sov_gad_2_ile) gad, sum(sov_gam_3_ile) gam,
sum(sov_gao_1_ile + sov_gad_2_ile + sov_gam_3_ile) / 3 average
from vrz
group by data)
select to_char(data, 'yyyy-mm-dd') data, gao, gad, gam, gao+gad+gam summary, average
from cte
union all
select 'ALL', sum(gao), sum(gad), sum(gam), sum(gao+gad+gam), sum(average)
from cte
union all
select 'AVG', round(avg(gao)), round(avg(gad)), round(avg(gam)), null, null
from cte
order by 1
DATA GAO GAD GAM SUMMARY AVERAGE
---------- ---------- ---------- ---------- ---------- ----------
2000-01-21 226 228 239 693 231
2000-01-22 426 428 436 1290 430
2000-01-23 301 302 225 828 276
ALL 953 958 900 2811 937
AVG 318 319 300