1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

count(*) by missing range

Discussion in 'SQL PL/SQL' started by mdilyas, Jan 12, 2011.

  1. mdilyas

    mdilyas Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello everyone,
    i have the requirement to print the summary of the missing range with count.

    i have data like

    select no from abc;

    100
    101
    102
    105
    106
    107
    108
    111
    112
    113
    115

    ;
    i want the result to be displayed in summary like

    100-102 3
    105-108 4
    111-113 3
    115-115 1

    ,
    can anyone pls help me
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Try this


    Code (SQL):
    SELECT '100-102' range,SUM(CASE WHEN a >=100 AND a <=102 THEN 1
        END) COUNT
    FROM t
    UNION
    SELECT  '105-108' range,SUM(CASE WHEN a >=105 AND a <=108 THEN 1
        END)
    FROM t
    UNION
    SELECT  '111-113' range,SUM(CASE WHEN a >=111 AND a <=113 THEN 1
        END)
    FROM t
    UNION
    SELECT  '115-115' range,SUM(CASE WHEN a >=115 AND a <=115 THEN 1
        END)
    FROM t
    /


    RANGE        COUNT
    ------- ----------
    100-102          3
    105-108          4
    111-113          3
    115-115          1
     
  3. mdilyas

    mdilyas Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello Sir,
    Its good, but i have the other scenario, my data is not fixed, its dynamic, may be the numbers will differ.. i want the missing number range, wherever the number missed it should give the count and then start from that number .... etc..

    thanx in advance
    ilyas
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Try This ..

    There could be simple methods for the same. . . Just play around it . ..

    Code (SQL):

    SQL> SELECT * FROM t;

             X
    ----------
           100
           101
           102
           105
           106
           107
           108
           111
           112
           113
           115

    11 ROWS selected.

    SQL> SELECT A, B,
      2         (SELECT COUNT(*)
      3          FROM T
      4          WHERE X BETWEEN A AND B) CNT
      5  FROM (
      6      SELECT  MN A, MIN( X) B
      7      FROM (
      8      SELECT X , LEAD(X) OVER (ORDER BY X) LD ,
      9                 MIN(X) OVER (ORDER BY NULL) MN
     10      FROM T )
     11      WHERE LD-X <>1
     12      GROUP BY MN
     13      UNION
     14      SELECT LD , NVL(LEAD(X)OVER (ORDER BY X),MX) LD1 FROM (
     15      SELECT X , LEAD(X) OVER (ORDER BY X) LD ,
     16                 MAX(X) OVER (ORDER BY NULL) MX
     17      FROM T )
     18      WHERE LD-X <>1);

             A          B        CNT
    ---------- ---------- ----------
           100        102          3
           105        108          4
           111        113          3
           115        115          1

    SQL>