CODEWITHSUNDEEP
sqlsqlite
Vivek Sehrawat
Vivek Sehrawat

Reputation: 6570

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

i have an sqlite table like Results and want to make a table like finalResults by counting the number of options. i am 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

Upvotes: 0

Views: 70

Answers (2)

peterm
peterm

Reputation: 92845

You can do it like this

INSERT INTO finalResults(questionId, option1, option2, option3, option4)
SELECT questionId,
       MIN(CASE WHEN optionid = 1 THEN n END) option1,
       MIN(CASE WHEN optionid = 2 THEN n END) option2,
       MIN(CASE WHEN optionid = 3 THEN n END) option3,
       MIN(CASE WHEN optionid = 4 THEN n END) option4
  FROM
(
  SELECT questionId, optionid, COUNT(*) n
    FROM results
   GROUP BY questionId, optionid
) q
 GROUP BY questionId

Here is SQLFiddle demo

or 

INSERT INTO finalResults(questionId, option1, option2, option3, option4)
SELECT questionId,
       SUM(CASE WHEN optionid = 1 THEN 1 ELSE 0 END) option1,
       SUM(CASE WHEN optionid = 2 THEN 1 ELSE 0 END) option2,
       SUM(CASE WHEN optionid = 3 THEN 1 ELSE 0 END) option3,
       SUM(CASE WHEN optionid = 4 THEN 1 ELSE 0 END) option4
  FROM results 
 GROUP BY questionId

Here is SQLFiddle demo

Output in both cases:

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

Upvotes: 2

Hitesh Siddhapura
Hitesh Siddhapura

Reputation: 1392

try following query 

select questionId,
sum(optionId==1) as option1,
sum(optionId==2) as option2,
sum(optionId==3) as option3
from results
group by questionId

or sqlfiddle http://sqlfiddle.com/#!7/415b7/6 (open into chrome & safari)

Upvotes: 0

Related Questions