Skip to content
Advertisement

Get data based on condition in oracle sql

My table

loads(Unique)       Value
 T123                11
 T234               9.5
 T456               15
 T678               35
 T345               3.7    

Want I want

 count(values<=10)    count(values>10 &<=20)   count(values>20) 
            2                       2                    1

I tried to use CASE but don’t know the usage

Advertisement

Answer

CASE yes; not with COUNT but with SUM:

SQL> with test (loads, value) as
  2    (select 't123', 11   from dual union all
  3     select 't234',  9.5 from dual union all
  4     select 't456', 15   from dual union all
  5     select 't678', 35   from dual union all
  6     select 't345',  3.7 from dual
  7    )
  8  select
  9    sum(case when value <= 10 then 1 end) cnt_1,
 10    sum(case when value > 10  and value <= 20 then 1 end) cnt_2,
 11    sum(case when value > 20 then 1 end) cnt_3
 12  from test;

     CNT_1      CNT_2      CNT_3
---------- ---------- ----------
         2          2          1

SQL>
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement