# Thread: how to create query ??????????

1. Registered User
Join Date
Sep 2003
Posts
12

## Unanswered: how to create query ??????????

create table qq
(qty number,
start_no number,
end_no number);

insert into qq values (1,1,100);
insert into qq values (1,101,200);
insert into qq values (1,201,300);
insert into qq values (1,5001,5100);
insert into qq values (1,7001,7100);
insert into qq values (1,7101,7200);

now
i have a table with following data
qty start_no end_no
1 1 100
1 101 200
1 201 300
1 5001 5100
1 7001 7100
1 7101 7200

i used query like
select sum(qty),min(start_no),max(end_no) from qq;

it show
6 1 7200

BUT I WANT
3 1 300
1 5001 5100
2 7001 7200

PLZ HELP ME????????????????

THANKS A LOT
kuljeet pal singh

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

## Re: how to create query ??????????

Please explain what that 3-line result is supposed to represent? I can't see it myself.

3. Registered User
Join Date
Sep 2003
Posts
12
I WANT
qty start_no end_no
3 1 300
1 5001 5100
2 7001 7200
if start_no and end_no in particule sequence then group it
else
create another group of another series

4. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
What "particule sequence" are you refering to? Please be more clear!

5. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
If you mean, values in range 1-1000, 1001-2000 etc. then this would do it:

select sum(qty),min(start_no),max(end_no)
from qq
group by trunc(start_no/1000);

6. Registered User
Join Date
Sep 2003
Posts
12
like

start_no-1=last_no of previous record;
if equal then it is a series

****LIKE THIS *******
particuler sequence is like
start_no last_no
1 100
101 200
201 300

(this is a sequence from 1 to 300) NO BREAK IN SEQUENCE
and
another
5001 5100
5101 5200
this sequence is start from 5001 to 5200

so two group is created
qty start_no end_no
3 1 300
2 5001 5200

7. Registered User
Join Date
Sep 2003
Posts
12
u r query will run but not according to my requirement
if another value is inserted
like
insert into qq values(1,5501,5600);
NOW data in table is

QTY START_NO END_NO
----- ---------- ----------
1 1 100
1 101 200
1 201 300
1 5001 5100
1 7001 7100
1 7101 7200
1 5501 5600
now i want

3 1 300
1 5001 5100
2 7001 7200
1 5501 5600 <----because it will not in series
if start_no is 5101 in place of 5501
then it will group in series of 5001
and 5001 series return qty 2 in place of 1

8. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
That's a lot harder, but I think this does it right:
Code:
```SQL> select sum(qty),min(start_no),max(end_no)
2  from qq,
3  ( select range_start,
4           nvl((lead(range_start) over (order by range_start))-1,999999999) range_end
5    from
6    ( select decode(ld,start_no-1,null,start_no) range_start
7      from
8      ( select start_no, end_no, lag(end_no) over (order by start_no) ld
9        from qq
10      )
11      where decode(ld,start_no-1,null,start_no) is not null
12    )
13  ) ranges
14  where start_no between ranges.range_start and ranges.range_end
15  group by ranges.range_start;

SUM(QTY) MIN(START_NO) MAX(END_NO)
---------- ------------- -----------
3             1         300
1          5001        5100
1          5501        5600
2          7001        7200```
To see how it works, start with the innermost query and work outwards.

(PS Why do you type "u r query" instead of "your query"? It only saves
1 keystroke, but it confuses the hell out of me!)

9. Registered User
Join Date
Sep 2003
Posts
12