I need to create an export process that aggregates our records based on common characteristics, but the aggregation also much have a total cost that is less than an arbitrary amount assigned by a variable. For example, suppose that you have:

1 Blue $1

2 Blue $1

3 Red $1

4 Red $2

And the limit is $2. The desired output is:

1 Blue $2

2 Red $2

3 Red $1

Using the standard COLOR, MAX(Cost) GROUP BY COLOR type of query will yield the first line, but the second line of the output above will be $3 and the last line will be absent.

Suppose further that the totalcost is actually a function of unitcost and quantity. So, the supplied data will really be:

1 Blue 2 $2

2 Blue 3 $2

3 Red 5 $4

4 Red 6 $4

Suppose the limit is $10. The desired output is:

1 Blue 5 $2 $10

2 Red 2 $4 $8

3 Red 2 $4 $8

4 Red 2 $4 $8

5 Red 2 $4 $8

6 Red 2 $4 $8

7 Red 1 $4 $4

Any suggestions on an elegant way to automate this? I need my data "lumpy" but not too "lumpy".

Thanks!