CODEWITHSUNDEEP
mysql
checcco
checcco

Reputation: 300

MySQL: SUM in WHERE clause

I've got this table

CREATE TABLE `subevents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) DEFAULT NULL,
  `content` text,
  `class` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

Each row can have a different value in the 'class' field.

I'd like to select any number of rows, ordered randomly, as long as the sum of the values in the 'class' field is equal to 100.

How could I accomplish it directly in the MySQL query without doing it later in PHP?

Thanks everybody!

Upvotes: 1

Views: 1658

Answers (2)

David Jafferian
David Jafferian

Reputation: 36

By "ordered randomly" I assume you mean that the order of the rows doesn't matter but no row can be used more than once. So you are looking for a combination of rows in which the sum of class equals 100. Use the brute force method. Randomly generate possible solutions until you find one that works.

delimiter //
CREATE PROCEDURE subsetsum(total)
BEGIN
    DECLARE sum INTEGER;
    REPEAT
        CREATE OR REPLACE VIEW `solution`
        AS SELECT * FROM `subevents`
            WHERE 0.5 <= RAND();
        SELECT SUM(`class`) INTO sum FROM `solution`;
    UNTIL sum = total END REPEAT;
END
//
delimiter ;
CALL subsetsum(100); /* For example */
SELECT * FROM `solution`;

I have tested this with tables having a TINYINT column of random values and it is actually reasonably fast. The only problem is that there is no guarantee that subsetsum() will ever return.

Upvotes: 2

poeschlorn
poeschlorn

Reputation: 12440

I don't think this is possible with only SQL...the only thing which comes to my mind is to redo a the sql query as long the sum isn't 100

But I have no clue how to select a random number of rows at once.

Upvotes: 0

Related Questions