# Thread: Finding a position based on SUM...

1. Registered User
Join Date
Nov 2004
Posts
35

## Unanswered: Finding a position based on SUM...

Consider a table with this structure:

id | amount
--------------
1, 10
2, 25
3, 10
4, 5

The SUM of the amount column is 50.

The question

Were I to randomly select a number from 1 to 50 (upper limit being the sum), and then randomly shuffle these columns, e.g.:

random select: 24

3, 10
1, 10
2, 25
4, 5

The number 24 falls at id 2 with the random ordering above:

First sum = 10, <24
Second sum = 20, <24
Third sum = 45 -> success

I'd like for my query to return "2", the ID of the column that added to the third sum.

Can I do this with a query? I'm doing it programmatically now, but thought this might be possible..

2. Registered User
Join Date
Sep 2009
Posts
64
Interesting problem ... Assuming that it's not random (i.e. sorted by ID), you can do it using this query:

Code:
```SELECT
t1.id, SUM(t2.amount) AS total
FROM
the_table t1
LEFT JOIN the_table t2 ON t2.key_id <= t1.key_id
GROUP BY
1
HAVING
total < 24
ORDER BY
t1.id```
The thing is ... you'll have to be able to find the sum of amount from the previous rows. I achieve this by self joining the table on id is less than or equal to existing row.

When you start randomizing the rows, you somehow need to know which rows to self join so that you get the right sum. Obviously by using id won't do it. What you need is to somehow get this result:

Code:
```3, 10, 3, 10

1, 10, 3, 10
1, 10, 1, 10

2, 25, 3, 10
2, 25, 1, 10
2, 25, 2, 25

4,  5, 3, 10
4,  5, 1, 10
4,  5, 2, 25
4,  5, 4,  5```
Hence when you group it by the first ID, you'll get the following result:

Code:
```3, 10, 10
1, 10, 20
2, 25, 45
4,  5, 50```
It's doable, I think, but because of the nature of random, you can't self join a random table with another random table as if it's truly random the tables would be different. You can select the rows randomly from the table and put it to temporary table and add row number in sequence and modify my query and run it on the temp table instead.

In short, you create a temp table as the result of random select similar to this (the first column is row_number; the second is your id, the third is value)
Code:
```1, 3, 10
2, 1, 10
3, 2, 25
4, 4,  5```

#### Posting Permissions

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