Solved

selecting rows base on sum of field

Posted on 2014-04-25
7
366 Views
Last Modified: 2014-05-10
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?
0
Comment
Question by:rafaelrgl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Accepted Solution

by:
plusone3055 earned 167 total points
ID: 40023181
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
 
LVL 1

Author Comment

by:rafaelrgl
ID: 40023212
i need to create kits to sell that total 150 each an each kit must be unique.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 40023238
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 167 total points
ID: 40023245
awking:
I agree with you as well
I could see a for loop done with .NET
or would a pivot table work here ?
0
 
LVL 27

Assisted Solution

by:skullnobrains
skullnobrains earned 83 total points
ID: 40028928
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
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 83 total points
ID: 40028980
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 40029343
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

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question