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!

how to find summary value by range

Discussion in 'SQL PL/SQL' started by berger_beer, Jul 22, 2011.

  1. berger_beer

    berger_beer Guest

    Hi,

    please help me

    this is my table

    create table sequence (
    id int not null primary key
    );

    insert into sequence(id) values
    (1), (2), (3), (4), (6), (7), (8), (9),
    (10), (15), (16), (17), (18), (19), (20),(22);

    i need the answer to group the sequence like this


    start_number | end_number | count
    1 | 4 | 4
    6 | 10 | 5
    15 | 20 | 6
    22 | 22 | 1


    what is the statement to do this?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):
    WITH rec AS (
    SELECT 1 ST_NUM, 4 ED_NUM FROM DUAL UNION
    SELECT 6 ST_NUM, 10 ED_NUM FROM DUAL UNION
    SELECT 15 ST_NUM, 20 ED_NUM FROM DUAL UNION
    SELECT 22 ST_NUM, 22 ED_NUM FROM DUAL )
    SELECT  ST_NUM,ED_NUM , (SELECT COUNT(*) FROM seq WHERE seq.seqid BETWEEN ST_NUM AND  ED_NUM)  
    FROM rec