Sql mysql query to retrieve specific numbers of rows randomly based on condition

I am developing an online exam application where I have a main question table that contains a field (name: qtype) containing data for the question type. The question type can be of the following three types:

• single
• plural
• decrease

The app generates a random question paper from this master table using the following query:

select * from mst_question where test_id = 1 ORDER BY RAND() LIMIT 25

      

        
        
        
      

    

This creates a random 25-question questionnaire for my online exam.

So far it works well ...

Now I need to implement a function in my application where 25 randomly generated questions (or any number - this will depend on the test ID) will always have a FIXED mix of different question types available in the main question table (mst_question) for each randomly generated set of questionnaires ...

Let's say if there are 108 questions in the main question table for a particular test id and all three question types are in the db for that test, it will provide the same number of different question types for every random query !! I wrote the following sql query to find out the percentage of each question type in the main question table.

So far I have tried this and came up with this sql query:

select qtype,count(*) as qtypetotal,(select count(*) from mst_question where test_id = 1) as totalqtype,round((count(*)/(select count(*) from mst_question where test_id = 1)*100),2) as qtypepercentage from mst_question where test_id = 1 group by qtype

      

        
        
        
      

    

Output of the following query:

qtype qtypetotal totalqtype qtypepercentage  
desc 24 108 22.22%  
multiple 34 108 31.48%  
single 50 108 46.30%  

I need to form a sql query that will give me 25 randomly generated questions, where 22.22% of 25 questions should be desc type, 31.48% of 25 questions should be multiple types, and the remaining 46.30% of 25 questions should be of the same type.

I'm stuck ....... Pls advise ...

Thank you in advance: -)

Thanks @MikeBrant .... I created a dynamic sql which is definitely the way I wanted ... just one problem now .... if I execute this sql query:

select qtype,round(25*(count(*)/(select count(*) from mst_question where test_id = 1))) as numquests from mst_question where test_id = 1 group by qtype

      

        
        
        
      

    

I am getting the following results:

"desc" "6" "multiple" "8" "single" "12"

and based on the above query, I create this dynamic query:

$dynamicsql[] =  "(SELECT * FROM mst_question WHERE test_id = 1 AND qtype = '".trim($rstype->qtype)."' ORDER BY RAND() LIMIT ".$rstype->numquests.")";

$finalsql = implode(" UNION ALL ",$dynamicsql)." ORDER BY RAND()";

      

        
        
        
      

    

I want to create a total of 25 random questions, but the sum of these types is 26 !!! and I get the question additionally: - (