Question about sums

gorivo · #1 (**permalink**) 04-03-06, 16:06

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!

gorivo · #2 (**permalink**) 04-04-06, 12:20

anyone?

healdem · #3 (**permalink**) 04-04-06, 13:35

have a look in the Mysql manual for random

Pat Phelan · #4 (**permalink**) 04-04-06, 14:27

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

gorivo · #5 (**permalink**) 04-04-06, 15:38

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

jfulton · #6 (**permalink**) 04-04-06, 16:29

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.

gorivo · #7 (**permalink**) 04-05-06, 13:40

any ideas how to do it without stored procedure or some other sort of scripting?

Pat Phelan · #8 (**permalink**) 04-05-06, 14:04

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

gorivo · #9 (**permalink**) 04-05-06, 14:11

Thank you, Pat!

jfulton · #10 (**permalink**) 04-05-06, 15:00

Quote:

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...

Internet Services

Web Development

Digital Marketing

Consumer Tech