SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      col1
    FROM
      table
    GROUP BY
      col1
    HAVING
      SUM(col2) = 17
    ORDER BY
      RAND()
    Edit:

    Oops! Still not what you want though

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried that but get an invalid use of the group function

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Quote 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. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

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