How to get rows having sum equal to given value
-
There is table contain
ID Qty
1 2
2 4
3 1
4 5Now if i had to choose rows where sum of Qty equals to 10, How can i do this ? like 2+4+1 = 7 but if i add 5 then 12 so ignore 2, then 4+1+5 = 10 How can i achieve this ? I want id's of that rows which contain combination/sum equal to 10 (number i put)
-
There is table contain
ID Qty
1 2
2 4
3 1
4 5Now if i had to choose rows where sum of Qty equals to 10, How can i do this ? like 2+4+1 = 7 but if i add 5 then 12 so ignore 2, then 4+1+5 = 10 How can i achieve this ? I want id's of that rows which contain combination/sum equal to 10 (number i put)
declare @qty as decimal(18,2) set @qty=(select qty from mytable where id=4) select sum(qty)-@qty from myTableBy this example you will have result: 2+4+1=7
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
-
There is table contain
ID Qty
1 2
2 4
3 1
4 5Now if i had to choose rows where sum of Qty equals to 10, How can i do this ? like 2+4+1 = 7 but if i add 5 then 12 so ignore 2, then 4+1+5 = 10 How can i achieve this ? I want id's of that rows which contain combination/sum equal to 10 (number i put)
-
There is table contain
ID Qty
1 2
2 4
3 1
4 5Now if i had to choose rows where sum of Qty equals to 10, How can i do this ? like 2+4+1 = 7 but if i add 5 then 12 so ignore 2, then 4+1+5 = 10 How can i achieve this ? I want id's of that rows which contain combination/sum equal to 10 (number i put)
To help you more how large would the table be that you have to loop through?
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
There is table contain
ID Qty
1 2
2 4
3 1
4 5Now if i had to choose rows where sum of Qty equals to 10, How can i do this ? like 2+4+1 = 7 but if i add 5 then 12 so ignore 2, then 4+1+5 = 10 How can i achieve this ? I want id's of that rows which contain combination/sum equal to 10 (number i put)
-
There is table contain
ID Qty
1 2
2 4
3 1
4 5Now if i had to choose rows where sum of Qty equals to 10, How can i do this ? like 2+4+1 = 7 but if i add 5 then 12 so ignore 2, then 4+1+5 = 10 How can i achieve this ? I want id's of that rows which contain combination/sum equal to 10 (number i put)
So you want to solve a knapsack problem[^] in SQL? You do realize the problem is NP complete? :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.4 -
So you want to solve a knapsack problem[^] in SQL? You do realize the problem is NP complete? :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.4 -
Exactly - ,subset sum problem[^]
:thumbsup:
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.4 -
So you want to solve a knapsack problem[^] in SQL? You do realize the problem is NP complete? :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.4the link you had given pointed me to Backpack I think what you mean is Knapsack_problem I realize the complexity. But at any given time max rows can b only 50-100,i am getting these rows from Select statement. Then from these rows i have to get rows whose sums equals to Qty given (input). So, will you please help me finding an algorithm to solve. Regards
-
To help you more how large would the table be that you have to loop through?
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
at any given time max rows can b only 50-100,i am getting these rows from Select statement. I put them in temp table, iterate over them in loop, something like that ?? Then from these rows i have to get rows whose sums equals to Qty given (input). Regards
-
the link you had given pointed me to Backpack I think what you mean is Knapsack_problem I realize the complexity. But at any given time max rows can b only 50-100,i am getting these rows from Select statement. Then from these rows i have to get rows whose sums equals to Qty given (input). So, will you please help me finding an algorithm to solve. Regards
The problem is actually known as the subset sum problem as konduc pointed out, with a useful link. As your number of rows is limited, you should load them all in memory and go for a normal solution, not a database-centric SQL one. Now start studying the keywords and links given to you, and help yourself with Google and/or some books. No one is going to do your job for you. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.4