₦airaland Forum

BobAxelrod:
I have a simple table:

col1 col2
1 2
2 3
3 7
4 1
5 9


I want to select random rows of (col1) where the cumulative sum of column (col2) equals a value

example:
select all random rows of col1 where the sum of col2 equals 17
returning: col1: 3, 4 and 5


Any ideas?

@dhtml and everyone

aubventure:
Which language are you working on

Noblecx:
Explain better, maybe with another illustration

BobAxelrod:
The table has just two columns col 1 and col 2

Col 1 is the ID, and col 2 is the amount

Let's say I need to select all rows where the sum equals 5 .
It should return row 1 and 2 coz the values there are 2 and 3 i.e 2+3 =5

How do I go about it

Noblecx:
OK, if I should get you clearly, you need to get all rows whereby the sum of col1 and col2 = 5?
Even can the sum be greater than 5?

BobAxelrod:
I have a simple table:

col1 col2
1 2
2 3
3 7
4 1
5 9


I want to select random rows of (col1) where the cumulative sum of column (col2) equals a value

example:
select all random rows of col1 where the sum of col2 equals 17
returning: col1: 3, 4 and 5


Any ideas?

@dhtml and everyone

godofbrowser:
Oh! if i get you clearly, you need rows where after summing their col2 values you get 17?

Example: col2: 7+9+1 = 17 hence you get rows 3,4, and 5 ?

BobAxelrod:
No, it shouldn't be greater than, if 5 is not available then the database shouldn't return any result

BobAxelrod:
No, it shouldn't be greater than, if 5 is not available then the database shouldn't return any result

Select col1, col2 
From yourTable
Where sum = 5
 IN
(Select col1, col2, (col1 + col2) as sum
From YourTable) 

imustsaymymindo:
Did I try?

Select Rand(col1) from table_name where sum (col2) is Not Null

Craigston:
How precise do you want the results to be? Do you want to carry out an exhaustive search of all such possible rows, and thus obtain every possible answer?
This seems like a combinatorial problem, and if you want an exhaustive enumeration of the problem, the cost of computing increases fast as the number of rows increases.
Also, are negative integers allowed in the rows? Are the number restricted to any set of numbers (integers, natural numbers, complex numbers, real numbers...).
I don't know how to implement this in a database, but you can do it in a program using a 1 x n matrix that maps to your table.

See these SO links, [1] and [2], for similar problems.
This problem is not one at which SQL can shine. It requires much use of repetition and the running grows exponentially. In fact, I wouldn't attempt it for N results where N > 3, N being the number of rows whose sum matches the expected result.

However you do it, I wish you good luck. I don't know what I'm saying, so do not assume it to be credible advice. I just came to say hello.
DanielTheGeek, dhtml18, Jregz, seunthomas, FincoApps, cbrass, FrankLampard, godofbrowser, thewebcraft, Javanian, booyakasha, larisoft, stack1, yawatide, tr3y, Knownpal, rayval, blueyedgeek, jidez007, KvnqPrezo, Jenifa123, VenantCode, directonpc, DavidTheGeek, crotonite, Blenyo11, CodeNister, CodeHouse, logicalhumour, Asalimpo, lekropasky...
Someone needs help.

Disclaimer: the mentioned monikers were semi-automatically generated.
Merry Christmas and a Happy New Year to all devs and techies.

[1] http://stackoverflow.com/questions/21020619/sql-server-select-rows-whos-sum-matches-a-value

[2] http://stackoverflow.com/questions/6289314/how-to-get-rows-having-sum-equal-to-given-value

Noblecx:
This is what I was able to come up with.

Select col1, col2 
From yourTable
Where sum = 5
 IN
(Select col1, col2, (col1 + col2) as sum
From YourTable) 

BobAxelrod:
which database is this, not working for me

Noblecx:
MSSQL

BobAxelrod:
which result did you get after running dz query

BobAxelrod:
which database is this, not working for me

BobAxelrod:
Too much grammar, thanks for the links though, seems the problem is not a small one after all

Craigston:
Please just return this problem to wherever you got it from. It's holiday man. This problem sef.

Jregz:
DI'd not get the mention nairaland's notification stuff is pretty "dumb".. I don't even know that much sql though

BobAxelrod:
I have a simple table:

col1 col2
1 2
2 3
3 7
4 1
5 9


I want to select random rows of (col1) where the cumulative sum of column (col2) equals a value

example:
select all random rows of col1 where the sum of col2 equals 17
returning: col1: 3, 4 and 5


Any ideas?

@dhtml and everyone

Viruses:
MSSQL SERVER:

select SimpleTable.col1 from SimpleTable where (select sum(SimpleTable.col2) from SimpleTable) = 17

PM me if it does not work.

BobAxelrod:
yes, do you know how I can achieve that

BobAxelrod:
I have a simple table:

col1 col2
1 2
2 3
3 7
4 1
5 9


I want to select random rows of (col1) where the cumulative sum of column (col2) equals a value

example:
select all random rows of col1 where the sum of col2 equals 17
returning: col1: 3, 4 and 5


Any ideas?

@dhtml and everyone

Craigston:
How precise do you want the results to be? Do you want to carry out an exhaustive search of all such possible rows, and thus obtain every possible answer?
This seems like a combinatorial problem, and if you want an exhaustive enumeration of the problem, the cost of computing increases fast as the number of rows increases.
Also, are negative integers allowed in the rows? Are the numbers restricted to any set of numbers (integers, natural numbers, complex numbers, real numbers...).
I don't know how to implement this in a database, but you can do it in a program using a 1 x n matrix that maps to your table.

See these SO links, [1] and [2], for similar problems.
This problem is not one at which SQL can shine. It requires much use of repetition and the running time grows exponentially. In fact, I wouldn't attempt it for N results where N > 3, N being the number of rows whose sum matches the expected result.

However you do it, I wish you good luck. I don't know what I'm saying, so do not assume it to be credible advice. I just came to say hello.
DanielTheGeek, dhtml18, Jregz, seunthomas, FincoApps, cbrass, FrankLampard, godofbrowser, thewebcraft, Javanian, booyakasha, larisoft, stack1, yawatide, tr3y, Knownpal, rayval, blueyedgeek, jidez007, KvnqPrezo, Jenifa123, VenantCode, directonpc, DavidTheGeek, crotonite, Blenyo11, CodeNister, CodeHouse, logicalhumour, Asalimpo, lekropasky...
Someone needs help.

Disclaimer: the mentioned monikers were semi-automatically generated.
Merry Christmas and a Happy New Year to all devs and techies.

[1] http://stackoverflow.com/questions/21020619/sql-server-select-rows-whos-sum-matches-a-value

[2] http://stackoverflow.com/questions/6289314/how-to-get-rows-having-sum-equal-to-given-value