Database

13-Aug-07 23:49

Hi
I have about 2k records in a table which contains a numeric field. I would like to know if there is any way I can select a set of records where the sum of this numeric field is limited to a particular value.

The table in question is part of an SQL 7 database.

Thanks in advance
Chandra

andyharman14-Aug-07 0:57

14-Aug-07 0:57

Hi Chandra

Do you mean:

select * from Orders where cust_id in (
   select cust_id from Orders
   group by cust_id
   having sum(order_total) > 500)
order by cust_id

This would receive the orders for customers who have placed more than $500 worth of orders.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

ChandraRam14-Aug-07 1:27

14-Aug-07 1:27

Hi Andy

Thanks for your answer... but what if my table contains all distinct cust_id?

Regards
Chandra

andyharman14-Aug-07 1:35

14-Aug-07 1:35

Hi Chandra

Sorry - I got a little confused because your original question stated "... where the sum of ...".

Does

select * from Orders where OrderValue > 500

do what you want?

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

ChandraRam14-Aug-07 1:53

14-Aug-07 1:53

Hi Andy,

I'm afraid this will not help either... let me explain with some data:

Field1  Field2<br />
3	34<br />
4	58<br />
5	97<br />
6	234<br />
7	345<br />
8	540<br />
9	679<br />
10	768<br />
11	2312<br />
12	4324

Now, I want to be able to pick up a set of records where the sum of Field2 is less than 5000.

I hope I have explained my requirement clearly Smile | :)

Thanks
Chandra

Modified to include /code tags since the tabs were swallowed up.

andyharman14-Aug-07 2:57

14-Aug-07 2:57

Hi Chandra

Thats a bit clearer - looks like some-sort of stock allocation problem. The simplest query would be:

select * from MyTable
  where Field1 in (
     select a.Field1
     from MyTable a
     inner join MyTable b
       on b.Field1 <= a.Field1
     group by a.Field1
     having sum(Field2) < 5000)
  order by Field1

The key is the link between a.Field1 and b.Field1. However, this checks rows sequentially. If you have a record with Field1=100 and Field2=1 then earlier rows take precedence.

If you want a more optimised allocation query then you would need to resort to a UDF.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

ChandraRam14-Aug-07 3:34

14-Aug-07 3:34

Thanks Andy

No... its not a stock-allocation problem, just something I am trying to implement without having to write a messy SP.

I ran your query and got this result... not quite what I was looking for, but thanks anyway.

field1	field2<br />
10       768<br />
11      2312<br />
4         58<br />
5         97<br />
7        345

andyharman14-Aug-07 5:34

14-Aug-07 5:34

Got it ....

create table #MyTable (
  Field1 int,
  Field2 int
  )
go

insert into #MyTable values (3, 34)
insert into #MyTable values (4, 58)
insert into #MyTable values (5, 97)
insert into #MyTable values (6, 234)
insert into #MyTable values (7, 345)
insert into #MyTable values (8, 540)
insert into #MyTable values (9, 679)
insert into #MyTable values (10, 768)
insert into #MyTable values (11, 2312)
insert into #MyTable values (12, 4324)
go

select * from #MyTable
  where Field1 in (
     select a.Field1
     from #MyTable a
     inner join #MyTable b 
       on b.Field1 <= a.Field1
     group by a.Field1
     having sum(b.Field2) < 5000)
  order by Field1
go

My previous solution had an ambiguous column.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

ChandraRam14-Aug-07 7:13

14-Aug-07 7:13

Hi, thanks for keeping at it still...

The result of the query is now only rows 10 and 11 Frown | :(

andyharman wrote:
My previous solution had an ambiguous column.

I had noticed it when I tried to run the query, and fixed it... thanks.

Blue_Boy14-Aug-07 3:03

14-Aug-07 3:03

select Field2,sum(Field2)as [Sum] from tablename where Field2< 5000<br />
group by Field2

I Love SQL

ChandraRam14-Aug-07 3:36

14-Aug-07 3:36

Blue_Boy wrote:
select Field2,sum(Field2)as [Sum] from tablename where Field2< 5000
group by Field2

Hi Blue_boy

I ran your query and got this result... not quite what I was looking for, but thanks anyway

Field2 Sum
34 34
58 58
97 97
234 234
345 345
540 540
679 679
768 768
2312 2312
4324 4324

Regards
Chandra

Blue_Boy14-Aug-07 4:52

14-Aug-07 4:52

In Field2 doesn't exists any value great then 5000.
Or tell me which values shouldn't display (from those values which you posted) when you get sum of filed2 which are gerat then 5000.

I Love SQL

ChandraRam14-Aug-07 5:18

14-Aug-07 5:18

Hi

The result I am looking for is:

Field1 Field2
3 34
4 58
5 97
6 234
7 345
8 540
9 679
10 768

The records with 11 and 12 will not figure in this selection since adding any of these Field2 to the sum of the Field2 above will push the total above 5000.

Regards
Chandra

Blue_Boy15-Aug-07 0:19

15-Aug-07 0:19

this should works

select * from tablename  where Field2 in<br />
 (select firstTable.Field2   from tablename firstTable     inner join tablename secondTable        <br />
on secondTable.Field2 <= firstTable.Field2 group by firstTable.Field2 having sum(secondTable.Field2) < 5000)  order by Field2

I Love SQL

ChandraRam15-Aug-07 1:20

15-Aug-07 1:20

It worked! Thank you.

Regards
Chandra

Blue_Boy15-Aug-07 1:25