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(st art_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]

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

"KULJEET" <kuljeet_twtpl@ hotmail.comwrot e...

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(st art_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(st art_no),max(end _no)
from <table_name>
where end_no < 301
union all
select sum(qty),min(st art_no),max(end _no)
from <table_name>
where start_no >= 5001 and end_no <= 7000
union all
select sum(qty),min(st art_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]

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

it's very easy to accomplish using recursion

[puppet_sock@hotmail.com]

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

kuljeet_twtpl@h otmail.com (KULJEET) wrote in message news:<febbed51. 0401160136.417f 42d1@posting.go ogle.com>...
[newbie sql question snipped]

Hint: look up group commands.
Socks

[KULJEET]

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

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 namevalues(1,55 01,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

[Siva Valiveru]

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

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