how to create query ??????????

[KULJEET]

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 <table_name>;

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

[Guido Konsolke]

"KULJEET" <ku***********@hotmail.com> wrote...

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 <table_name>;

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

Eat this:
select sum(qty),min(start_no),max(end_no)
from <table_name>
where end_no < 301
union all
select sum(qty),min(start_no),max(end_no)
from <table_name>
where start_no >= 5001 and end_no <= 7000
union all
select sum(qty),min(start_no),max(end_no)
from <table_name>
where start_no >= 7001 and end_no <= 7200;

If that doesn't fit your needs, start thinking.

[AK]

it's very easy to accomplish using recursion

[puppet_sock]

ku***********@hotmail.com (KULJEET) wrote in message news:<fe**************************@posting.google. com>...
[newbie sql question snipped]

Hint: look up group commands.
Socks

[KULJEET]

its a example only
i have thousand of rows in my table
AND I WANT TO GROUP ACCORDING TO PARTICULER SEQUENCE
if start_no and end_no in particule sequence then group it
else
create another group of another series
start and end no means:-
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

if i insert new values in table
insert into <table name> values(1,5501,5600);

so now 3 group is created
3 1 300
2 5001 5100
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 3 in place of 2

thanks
kuljeet pal singh

[AK]

Assuming that the intervals do not overlap,
we can also use ROW_NUMBER() and avoid recursion:

1. Create 2 table expressions:
(SELECT START_NO, (ROW_NUMBER() OVER(ORDER BY START_NO)) AS SERIES_NUM
FROM INTERVALS I WHERE NOT EXISTS(SELECT END_NO FROM INTERVALS I1
WHERE I1.END_NO+1=I.START_NO)) AS LOWER_ENDS

(SELECT END_NO, (ROW_NUMBER() OVER(ORDER BY END_NO)) AS SERIES_NUM
FROM INTERVALS I WHERE NOT EXISTS(SELECT START_NO FROM INTERVALS I1
WHERE I.END_NO+1=I1.START_NO)) AS HIGHER_ENDS

2. Join them on SERIES_NUM

SELECT START_NO, END_NO
FROM LOWER_ENDS JOIN HIGHER_ENDS
ON LOWER_ENDS.SERIES_NUM = HIGHER_ENDS.SERIES_NUM

[Siva Valiveru]

Well see the magic of "analytic function" ...

select * from t_analytic;

QTY START_ID END_ID
---------- ---------- ----------
1 1 100
1 1 100
2 101 200
1 101 200
3 201 300

select distinct sum(qty) over(partition by start_id,end_id) sqty,
min(start_id) over(partition by start_id,end_id) mstart_id,
max(end_id) over(partition by start_id,end_id) xend_id
from t_analytic
/

SQTY MSTART_ID XEND_ID
---------- ---------- ----------
2 1 100
3 101 200
3 201 300
Regards,
Siva