selecting rows base on sum of field

hi i have one table like this:

TBPRODUCT( ID AS INTEGER, PRODUCT AS STRING, PRICE AS DOUBLE )

this table have 500 rows and i want to select 6 products at a time that mach sum of price = 150.

and on the second time i select i don't want the same products i select on the first time.

how can i accomplish this?
LVL 1
rafaelrglAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
plusone3055Connect With a Mentor Commented:
can you please explain what the purpose of this function is ?

I understand what your asking for

500 Rows

select 6 rows that will total a price of 150
select another 6 rows (not including the previously selected 6 rows) also totaling 150

Can you elaborate on the purpose for this equation ?
0
 
rafaelrglAuthor Commented:
i need to create kits to sell that total 150 each an each kit must be unique.
0
 
awking00Connect With a Mentor Commented:
We understand what you're trying to do, but I'm not sure you can do this with pure sql. What are the minimum and maximum prices? Do the contain cents or are they even dollars? What is the average price? Can you post just the product_id and price (we don't need to know what the products are) for the 500 row table? Is there any tolerance (e.g. would one kit of 149.95 and another of 150.05 be acceptable)?  I also assume you would want the maximum number of kits that can be created.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
plusone3055Connect With a Mentor Commented:
awking:
I agree with you as well
I could see a for loop done with .NET
or would a pivot table work here ?
0
 
skullnobrainsConnect With a Mentor Commented:
in order to "select rows based on a field's sum" you would use something like this in sql

select sum(field) as sum, ...
from table
group by whatever
having sum=150

but then given what is understand from your goals this will not help since sql will not try every possible grouping by itself
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

to do as requested with pure SQL would require 5 cross joins, producing a table that is 500^6 rows!

And who said those kits would make sense?

Imagine, if you will, a combined plumbers and electrical merchant - we had a national chain doing this in my neck of the woods, but for the purposes of illustration please bear with me - what sense does it make to build a kit out of an oven and some low rated fuse wire and a toilet cistern flush valve?

And generally, kits are sold as such for the whole costing less than the individual parts, so there is some rounding involved as well.

Regards
  David
0
 
awking00Connect With a Mentor Commented:
Can you post just the prices? There may be a means of at least approximating a sum of $150 for kits of six products, but that possibility diminishes if there can be a price of $145, for example, or if the average product price is no where near $25.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.