Sum Of mysql table record

Question

I have a table named tbl_Question and a column named INT_MARK which has different marks for different questions. Like this:

VH_QUESTION     INT_MARK
-----------     --------
Q1              2
Q2              4

My question is: How to get a random set of 20 questions whose total sum of marks is 50?

Take a look at [this](http://stackoverflow.com/q/4329396/245679) — MD Sayem Ahmed, Nov 20 '12 at 05:18
You'll need an algorithm, this is more a mathematical question, once you know the strategy, it will be to convert it to SQL query! — Alexandre Lavoie, Nov 20 '12 at 05:19
A start: a) Pick 20 random questions. b) Do they add up to 50? c) If not, repeat. — tadman, Nov 20 '12 at 05:20
SQL is about set-based solutions, while this problem, even though it's dealing with a data set, should probably be solved iteratively, and that generally isn't SQL's strongest suit. — Andriy M, Nov 20 '12 at 12:45

score 1 · Answer 1 · answered Nov 20 '12 at 05:23

1

select VH_QUESTION, sum(INT_MARK) from tbl_Question
 group by VH_QUESTION
having sum(INT_MARK) > 50 
order by rand() limit 1

answered Nov 20 '12 at 05:23

solaimuruganv

27,177
1
18
23

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

I think this question may help you - seems a very similar problem.

If that don't work, I'd try to divide the problem in two: first, you make a combinatory of your questions. Then, you filter them by it's sum of points.

I couldn't find, however, how to produce all combinations of the table. I don't know how difficult that would be.

edited May 23 '17 at 10:24

Community

1
1

answered Nov 20 '12 at 05:22

mgarciaisaia

14,521
8
57
81

score 0 · Answer 3 · answered Nov 20 '12 at 05:24

0

select VH_QUESTION, sum(INT_MARK) from tbl_Question
group by VH_QUESTION
having sum(INT_MARK) >= 50 
order by rand() limit 20

answered Nov 20 '12 at 05:24

AnandPhadke

13,160
5
26
33

score 0 · Answer 4 · answered Nov 20 '12 at 05:55

Quick answer

SELECT * ,SUM(INT_MARK) as total_mark FROM tbl_Question  
GROUP BY VH_QUESTION
HAVING total_mark="50"
ORDER BY RAND()
LIMIT 5

it returns 0 line when no answers are possible but each time it finds one the questionsare random.

You could check the benchmark to see if you can have a faster query for large tables.

Sum Of mysql table record

4 Answers4