Results 1 to 2 of 2
  1. #1
    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..

    Thanks for your help, and your attempt at deciphering this explanation!

  2. #2
    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
  •