Oracle SQL UNION alternative

I’m using ORACLE SQL (11g), lets assume we have a table named TRANSMISSIONS which has field that holds file sizes

I want to perform various aggregate functions on various partitions let’s say for different file sizes. However I want the partitions to be cumulative. So a 10 KB file will be both in the <=500000 bytes partition and the <=2000000000 partition. So if I had 5 files <=500000 and 5 files > 50000 && < 2000000000 Then I would have the following reults:

   label       | number
---------------|-------
<=500000       | 5
<=2000000000   | 10

So basically the initial approach was to do unions like this:

SELECT '<=500000' as label,
       COUNT(1) AS numberFiles,
       round(avg(tra.TRA_SIZE)) as averageSize,
       sum(tra.TRA_SIZE) as totalSize
FROM TRANSMISSION tra
where tra.TRA_SIZE <= 500000

UNION

SELECT '<=2000000000' as label,
       COUNT(1) AS numberFiles,
       round(avg(tra.TRA_SIZE)) as averageSize,
       sum(tra.TRA_SIZE) as totalSize
FROM TRANSMISSION tra
where tra.TRA_SIZE <= 2000000000;

However if I have several of these partitions which I do it will result into a huge unmaintanable query where the only thing that changes basically is the label and the where clause.

Is there any better way to do this?

Answer

You are looking for cumulative sums. I think I would go for:

SELECT l.label, 
       COUNT(*) AS numberfiles,
       ROUND(AVG(t.tra_size), 2) AS averagesize,
       SUM(t.tra_size) AS totalsize
FROM #TRANSMISSION t JOIN
     (SELECT 1 as ord, '<=500000' as label, 0 as lo, 500000 as hi FROM DUAL UNION ALL
      SELECT 2 as ord, '<=2000000000' as label, 0 as lo, 2000000000 as hi FROM DUAL
     ) l
     ON t.tra_size BETWEEN l.lo AND l.hi
GROUP BY l.label, l.ord
ORDER BY l.ord;

This gives you full flexibility on the ranges that you want — I am starting at 0, assuming that is the minimum possible value but the code could be adjusted if this is not the case.