SitePoint Sponsor

# Thread: Select all rows where the sum of column equals a value

1. ## Select all rows where the sum of column equals a value

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?

2. Code:
```SELECT
col1
FROM
table
GROUP BY
col1
HAVING
SUM(col2) = 17
ORDER BY
RAND()```
Edit:

Oops! Still not what you want though

3. dan, that won't work, you can't use SUM in the WHERE clause

aidin, i have a feeling that you're not going to be able to get what you want

why col1: 3, 4 and 5? why not some other rows? surely those 5 rows aren't the only ones in the table?

i think you'll just have to return N random rows (where N is some number that you specify with LIMIT) and look for the sums in your application language (php or whatever)

4. I've tried that but get an invalid use of the group function

5. Originally Posted by Aidin
I want to select random rows of (col1) where the cumulative sum of column (col2) equals a valu
This should be done in the application language.

6. Now that I understand what you're asking, I can tell you there is no efficient way to compute these random groups. This is the Subset Sum problem, which is NP-complete, meaning it's not possible to compute in polynomial time as a function of the number of rows. Hopefully you don't have many rows

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•