-
sum of the range
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
-
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.
-
Try this:
Code:
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
/
Ales The whole difference between a little boy and an adult man is the price of toys
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|