Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Exclamation Unanswered: Question about sums

    Hello,

    I have mysql table, named "countries":

    country | number
    -----------------
    finland | 40
    germany | 30
    belarus | 15
    france | 25
    swiss | 21

    I need to select 3 countires from this table randomly where the sum of numbers is less then 80.

    For example

    Good example:
    finland(40) + belarus(15) + swiss(21) = 76
    ( 76 is less than 80 )

    Bad example:
    finland(40) + belarus(15) + germany(30) = 85
    ( 85 is greater than 80 )

    so how to do it ?

    I hope you understood my question.
    Waiting for your answers!
    Thank you in advance!

  2. #2
    Join Date
    Apr 2006
    Posts
    5
    anyone?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look in the Mysql manual for random
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you allowed to pick the same country more than once (in other words, is Belarus, Belarus, Belarus a valid combination for you)? Describe what you mean by random, since I don't think that even 1 percent of the folks that use that term in a problem description really mean random.

    You'll probably get a lot better response if you describe your real problem, in real world terms. This description sounds like a "slipery slope" where every answer is wrong, but you can't find that out without trying at least a dozen answers first.

    -PatP

  5. #5
    Join Date
    Apr 2006
    Posts
    5
    Hello,

    first of all, thank you for your replies!
    second of all, i am not allowed to pick the same country more than once (the column 'country' is unique).

    To make you understand the problem, here is mysql query, which selects 3 countries by random without any sums:

    Code:
    SELECT * FROM `countries` ORDER BY rand() LIMIT 3
    The valid result of this query may look like this:

    country | number
    =============
    finland | 40
    belarus | 15
    germany | 30

    As we can see, the sum of field, named "number", is 85 (40 + 15 + 30).

    So, what i need is to add one more condition to query above: the sum of randomly selected numbers must be less than 80. In this case, finland(40) + belarus(15) + germany(30) = 85 is not valid result, because 85 is greater than 80.

    In other words, mysql query should look something like this:

    Code:
    SELECT * FROM `countries` 
    WHERE sum_of_all_three_numbers_selected_randomly < 80 
    ORDER BY rand() LIMIT 3
    I think subqueries must be used, but i'm not sure

    I hope now you understood my problem
    Waiting for your replies

  6. #6
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Can this be done without a stored procedure or some other sort of scripting?

    I was thinking of having subqueries which successively selected "random" countries less than the remaining difference from 80 (that made sense...right? ) - but this could potentially leave you with one, or even, two nulls.

  7. #7
    Join Date
    Apr 2006
    Posts
    5
    any ideas how to do it without stored procedure or some other sort of scripting?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't have a MySQL engine to test against here at work, but the following works fine in Microsoft SQL 2000. You should be able to adapt it fairly easily:
    Code:
    CREATE TABLE #dbforums (
       Country	VARCHAR(20)
    ,  dbforums	INT
       )
    
    INSERT INTO #dbforums (country, dbforums)
       SELECT 'finland', 40 
       UNION SELECT 'germany', 30 
       UNION SELECT 'belarus', 15 
       UNION SELECT 'france', 25 
       UNION SELECT 'swiss', 21
    
    SELECT *
       FROM #dbforums AS a
       JOIN #dbforums AS b
          ON (a.country < b.country)
       JOIN #dbforums AS c
          ON (b.country < c.country)
       WHERE a.dbforums + b.dbforums + c.dbforums < 80
    It produces:
    Code:
    country              country              country              
    -------------------- -------------------- -------------------- 
    belarus              finland              swiss
    belarus              france               germany
    belarus              france               swiss
    belarus              germany              swiss
    france               germany              swiss
    -PatP

  9. #9
    Join Date
    Apr 2006
    Posts
    5
    Thank you, Pat!

  10. #10
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Can this be done without a stored procedure or some other sort of scripting?

    I was thinking of having subqueries which successively selected "random" countries less than the remaining difference from 80 (that made sense...right? ) - but this could potentially leave you with one, or even, two nulls.
    ...wow, brain fart...

    Populate and use a lookup table like Pat said...

Posting Permissions

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