sql - how to find out the duplicates and count them in another table -

i have sqlite table results , want make table finalresults counting number of options. using sqlite

   table name: results           table name: finalresults    ___________ _________         __________  _______  ________  _______  _______     questionid| optionid         questionid| option1 | option2 |option3 |option4     --------------------         -----------------------------------------------            40     | 3                40        | 1       |  2      | 4     | 1        40     | 3                41        | 2       |  1      | 1      | 3        40     | 2        40     | 4        40     | 2        40     | 1        40     | 3        40     | 3        41     | 1        41     | 3        41     | 2        41     | 4        41     | 4        41     | 4        41     | 1 

thank you

you can this

insert finalresults(questionid, option1, option2, option3, option4) select questionid,        min(case when optionid = 1 n end) option1,        min(case when optionid = 2 n end) option2,        min(case when optionid = 3 n end) option3,        min(case when optionid = 4 n end) option4   (   select questionid, optionid, count(*) n     results    group questionid, optionid ) q  group questionid 

here sqlfiddle demo

or

insert finalresults(questionid, option1, option2, option3, option4) select questionid,        sum(case when optionid = 1 1 else 0 end) option1,        sum(case when optionid = 2 1 else 0 end) option2,        sum(case when optionid = 3 1 else 0 end) option3,        sum(case when optionid = 4 1 else 0 end) option4   results   group questionid 

here sqlfiddle demo

output in both cases:

 | questionid | option1 | option2 | option3 | option4 | ------------------------------------------------------ |         40 |       1 |       2 |       4 |       1 | |         41 |       2 |       1 |       1 |       3 |