I have a data set similar to below and I'm trying to group the data into percentage ranges. It doesn't have to be a single query.

I want to pull the rows where Number A is 0-6% - of Number B, so I would expect to only pull the first row in the example data set for this first bucket. I'm also trying to do the same thing but for 7-15%, 16-20% and so forth.

I've tried the following but my brain is fried so I don't feel confident that this is the right approach. Select * from table WHERE abs(NumberA/NumberB * 100) >= 0 and abs(NumberA/Number * 100) <= 6

CodePudding user response：

You can use CASE to compute the range for each pair. For example:

select t.*, 
  case when abs(b / a - 1.0) < 0.06 then '0-6%'
       when abs(b / a - 1.0) < 0.15 then '6-15%'
       when abs(b / a - 1.0) < 0.20 then '15-20%'
       else '20% ' end as segment       
from t

Result:

 A    B    SEGMENT 
 ---- ---- ------- 
 105  100  0-6%    
 115  134  15-20%  
 120  110  6-15%   
 140  170  20%     
 140  190  20% 

See running example at db<>fiddle.

If you want to get only rows in the 0-6% range you can do:

select * 
from t
where abs(b / a - 1.0) < 0.06

Grouping is trivial when you have the ranges.