Return rows where sum of a single column is equal to "16" in mysql

Question

my project is a question paper generator for each question mark may varies based on the question type, i need to filter out some random question where based on the mark field

eg

only one table

Questions | Mark
q1        | 4
q2        | 4
q3        | 8
q4        | 6
q5        | 12
q6        | 2

i want to select the rows when we SUM(mark) is equal to 16

the result may varie like as follows

q1   | 4
q2   | 4
q4   | 6
q5   | 2

when we sum this result we will get 16 like this i need a query

my query is

Select question, mark 
from table 
where sum(mark) = 16

I dont understand what you say. Please take the time to explain your logic. — Juan Carlos Oropeza, May 23 '17 at 06:46
i have 2 colums "Quesion", "Mark" in my table, i need to select some random rows from these 2 column based on the SUM(Mark) meets to a value 16 — Aravindan, May 23 '17 at 06:49
So the query should add some row until `mark` reaches 16? Then display those rows? — Carl Binalla, May 23 '17 at 06:50
If this is possible to do in MySQL, it will be complicated. It would be much easier to just read all the marks into a PHP array, and then use a PHP loop to find the elements that add up to 16. — Barmar, May 23 '17 at 06:53
The logic is very complicated NP problem and become exponential more difficult based on number of rows, You need calculate all combination adding 16 and then select one at random. There are already algorithm for that. Check this one https://stackoverflow.com/questions/4632322/finding-all-possible-combinations-of-numbers-to-reach-a-given-sum — Juan Carlos Oropeza, May 23 '17 at 06:56
https://stackoverflow.com/questions/2667664/php-find-two-or-more-numbers-from-a-list-of-numbers-that-add-up-towards-a-given/2669545#2669545 — Barmar, May 23 '17 at 06:57
@JuanCarlosOropeza If the numbers are all positive, the problem can be simplified. You can sort the numbers, then you only have to search the ones that are less than 16. — Barmar, May 23 '17 at 06:58
@Barmar Sorting help, but still you can have `{1,15}` or `{8,8}` so is more complicated than that. I remember using a dynamic programing (DP) to solve the [Knapsack problem](https://en.wikipedia.org/wiki/Knapsack_problem) I guess is this also need some DP — Juan Carlos Oropeza, May 23 '17 at 07:01
This is possible- but the solution scales poorly, meaning it will be slow on larger data sets. — Strawberry, May 23 '17 at 07:25

Oscar Zarrus · Accepted Answer · 2017-05-24T06:05:33.120

The effort will be made by the computer, but if you have a large amount of data, take a coffee in the meantime.

/* ===== TAKEN FROM PHP.net ====== */
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$query = "Select question, mark from table"; // your query

$arrayData = array();//set an empty array to populate

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {

        //for calculation purpose, we force the Mark field to be an integer
        $arrayData[$row['question']] = (int)$row['mark']; //populate array with sql results
    }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();

Now we have the SQL results in the $arrayData variable. Like this:

Array("q1"=>4, "q2"=>4, "q3"=>8)

and so on...

Let's go.

 $targetSUM = 16;
//First of all, remove any doubt and see 
//if the sum of the entire array is the same as our target

if($targetSUM === array_sum($arrayData)){
    print_r($arrayData);
    die();
}
$i=0;
$results = array();
foreach($arrayData as $questionA=>$markA){

    $thisCycle = array($questionA=>$markA);
    foreach($arrayData as $questionB=>$markB){
        if ($questionA === $questionB) continue; // avoid sum SUM itself

        $thisCycle[$questionB] = $markB;

        $thisCycleSUM = array_sum($thisCycle);

        switch(true){
            case $thisCycleSUM < $targetSUM: //if results not reach our target, go on
                continue;
            break;
            case $thisCycleSUM === $targetSUM: //if results match, store and go on
               $results[$i] = $thisCycle;
               $thisCycle = array($questionA=>$markA);
            break;
            case $thisCycleSUM > $targetSUM: //if results exceeds, remove last element and go on
                unset($thisCycle[$questionB]);
            break;
        }

    }


        // Sorting procedure to avoid same combinations and no-combination
    if(isset($results[$i])){ 
        ksort($results[$i]); 
        $results[implode(array_keys($results[$i]))] = $results[$i];
        unset($results[$i]);
        $i++;
    }

}
    $results = array_values($results);
   print_r($results);

DEMO HERE

Your code saves me. Thanks buddy, this may help many one in future. — Aravindan, May 23 '17 at 11:58
You're welcome. Remember to mark the answer as valid to help many one in future — Oscar Zarrus, May 23 '17 at 19:50
Doh! I didn't foresee the absence of combinations. Code and Example modified. Thanks! — Oscar Zarrus, May 24 '17 at 06:14

Return rows where sum of a single column is equal to "16" in mysql

1 Answers1