1

Is it possible to solve this situation by sql query in ORACLE?

I have a table like this:

TYPE    UNIT
A       230
B       225
C       60
D       45
E       5
F       2

I need to separate units to the three(variable) 'same'(equally sized) intervals and foreach figure out the count? It means something like this:

0   - 77    -> 4
78  - 154   -> 0
155 - 230   -> 2
Petr
  • 1,193
  • 1
  • 15
  • 27
  • Want to try that again? I have no idea how you got from the table data to your desired output. A more detailed explanation of what you're trying to do would be helpful....What you've written makes no sense to me. – Mark J. Bobak Aug 06 '14 at 14:46

2 Answers2

3

You can use the maximum value and a connect-by query to generate the upper and lower values for each range:

select ceil((level - 1) * int) as int_from,
  floor(level * int) - 1 as int_to
from (select round(max(unit) / 3) as int from t42)
connect by level <= 3;

  INT_FROM     INT_TO
---------- ----------
         0         76 
        77        153 
       154        230

And then do a left outer join to your original table to do the count for each range, so you get the zero value for the middle range:

with intervals as (
  select ceil((level - 1) * int) as int_from,
    floor(level * int) - 1 as int_to
  from (select round(max(unit) / 3) as int from t42)
  connect by level <= 3
)
select i.int_from || '-' || i.int_to as range,
  count(t.unit)
from intervals i
left join t42 t
on t.unit between i.int_from and i.int_to
group by i.int_from, i.int_to
order by i.int_from;

RANGE      COUNT(T.UNIT)
---------- -------------
0-76                   4 
77-153                 0 
154-230                2
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
2

Yes, this can be done in Oracle. The hard part is the definition of the bounds. You can use the maximum value and some arithmetic on a sequence with values of 1, 2, and 3.

After that, the rest is just a cross join and aggregation:

with bounds as (
      select (case when n = 1 then 0
                   when n = 2 then trunc(maxu / 3)
                   else trunc(2 * maxu / 3)
              end) as lowerbound,
             (case when n = 1 then trunc(maxu / 3) 
                   when n = 2 then trunc(2*maxu / 3)
                   else maxu
              end) as upperbound
      from (select 1 as n from dual union all select 2 from dual union all select 3 from dual
           ) n cross join
           (select max(unit) as maxu from atable t)
     )
select b.lowerbound || '-' || b.upperbound,
       sum(case when units between b.lowerbound and b.upperbound then 1 else 0 end)
from atable t cross join
     bounds b
group by b.lowerbound || '-' || b.upperbound;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786