sum of the range
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: sum of the range

  1. #1
    Join Date
    Feb 2005
    Posts
    10

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  4. #4
    Join Date
    Feb 2005
    Posts
    10
    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
  •  


Click Here to Expand Forum to Full Width