sum of the range

[deepasona]

Hi

i have some data like this


select count(subs_id) "Number of subscribers" , cnt "Number of Profiles"
from ( select subs_id,count(*) cnt
from subscriber_INFO
where caller_type=1
group by subs_id)
group by cnt

The output will be like this

Subcr profiles
ibers

18 1
9 2
6 3
5 4
1 5
2 8
1 10

There will be millions of subscribers and each might have upto 50 profiles or something.

so i want the output something like


Number of subscribers Range of profile [ 1-10 ]

ie... this many number of subscribers having profiles ranging 1-10


33 1-10
24 11-20
8 21-30

like this..

this has to be in a single query..

pls help

[slimdave]

The CASE statement allows you to group the number ranges like this by returning the string "1-10" or "11-20" based on the value of cnt.

[ales]

Try this:

select
  num "Number of subscribers",
  to_char(cnt+1,'999')||' -'||to_char(cnt+10,'999') "Number of Profiles" 
from
(
  select count(subs_id) num , trunc(cnt-1,-1) cnt 
  from
  (
    select subs_id,count(*) cnt 
    from subscriber_INFO 
    where caller_type=1 
    group by subs_id
  ) 
  group by trunc(cnt-1,-1)
)
order by cnt
/

[deepasona]

This worked for ranges of 10


SELECT sum(subs_id) "No of subscribers",(cnt-9)||'-'||cnt "Range of CLI Based Profiles"
FROM (
select count(subs_id) subs_id ,(ceil(cnt/10))*10 cnt
from ( select subs_id,count(*) cnt
from subscriber_INFO
where caller_type=1
group by subs_id)
group by cnt
)
GROUP BY (cnt-9)||'-'||cnt ,(cnt-9) order by (cnt-9);

Thanks for your replies