sum() to a max value

[ameyemad]

I'm trying to randomly select records using sum, but to a maximum total only. How would this be done?

 

TABLE 1 - users

--------------------

username

 

TABLE 2 - accounting

--------------------

amount

username

 

 

I want to randomly select as many records in table 1 so that ALL sum(amount) total UNDER a specific amount. so basically i need to add up each sum(amount) or something, but I have no idea how to do this.

 

I know that selecting sum(amount) gives me the total for each username, but I want a list so the total of all sum(amounts) is less than a number I specify.

 

Any help would be great!

 

Thank you in advance.

[ameyemad]

Any help would be greatly appreciated :-)

[ameyemad]

Do you require more information? This has really got me stumped, and there might not be a way of doing it as I've tried searching all over.

 

Thanks

[Mchl]

I think you cannot do this with a simple query. A stored procedure might be necesseary...

[fenway]

Can you provide some sample output?

[ameyemad]

Okay sure.

 

So the 2 tables...

TABLE 1 - users
--------------------
username

TABLE 2 - accounting
--------------------
amount
username

 

Some data in table 1:

bob

jill

mary

hank

steven

 

Some data in table 2:

bob 55

jill 76

mary 11

bob 8

hank 12

hank 9

steven 77

jill 74

 

Ok, so let's say I want to find users from table 2 that have an amount totalling 120. So the output would be:

 

steven 77

hank 21

mary 11

 

And that's all that's displayed as that all that equals 120 or under. This can be randomized though as there is other combinations in the table which could equal 120 or under, but you should be able to understand what I'm after.

[fenway]

Those don't total 120... what am I missing?

[ameyemad]

And that's all that's displayed as that all that equals 120 or under. This can be randomized though as there is other combinations in the table which could equal 120 or under, but you should be able to understand what I'm after.

 

See bold text please. I want to get the closest to grouping amount possible to the amount 120.

[fenway]

Well, if you need to ensure you get back at least one group of three, then you'll have to loop through groups of three repeatedly... I suppose you could "loop" in an SP or in application code.  A simple SUM() with LIMIT 3 can help you out.