CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user2233540
user2233540

Reputation: 447

How to retrieve the amount of values according to a range of values in Oracle SQL

I'd like some help with Oracle SQL. Let's suppose the following example with two tables:

enter image description here

There is a way to know how many boxes there are in each range according to the Size of the boxes like this: 

 Small   3 
 Medium  1
 Large   1

I'm trying the following sentence, but it does't work fine:

SELECT Sizes.description, COUNT(*) FROM Sizes, Packets WHERE 
Packets.Size BETWEEN SUBSTR(Sizes.range,1,2) 
AND SUBSTR(Sizes.range,4)
GROUP BY Sizes.description;

Upvotes: 0

Views: 76

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44961

select nvl(s.description,'UNKNOWN'),count(*) 
from packets p left join s 
on p.size between to_number(regexp_substr(s.range,'^[^-]+')) 
and to_number(regexp_substr(s.range,'[^-]+$'))
group by s.description

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You should modify the sizes table to have a minimum and maximum size:

alter table sizes add minsize number;
alter table sizes add maxsize int;

update sizes
    set minsize = CAST(SUBSTR(range, 1, INSTR(range, '-') - 1 as number),
        maxsize = CAST(SUBSTR(range, INSTR(range, '-') + 1) as number;

Then use a regular join:

select s.range, count(*)
from packets p left join
     sizes s
     on p.size between s.minsize and s.maxsize
group by s.range
order by max(s.minsize);

You can do the same thing with a CTE, but fixing the table is the better idea:

with s as (
      select s.*, 
             CAST(SUBSTR(range, 1, INSTR(range, '-') - 1 as number) as minsize,
             CAST(SUBSTR(range, INSTR(range, '-') + 1) as maxsize
      from sizes
     )
select s.range, count(*)
from packets p left join
     s
     on p.size between s.minsize and s.maxsize
group by s.range
order by max(s.minsize);

Upvotes: 3

Related Questions