CODEWITHSUNDEEP
mysqlsql
Dave
Dave

Reputation: 387

get counts and percentages within groups

I have a table of data that contains question number and response data.

I need to be able to get a count of responses for each option of each question, and also what percentage that represents.

For example data may look like

questionNum   ResponseNum
1             1
1             2
1             2
1             2
1             3
2             1
2             1
2             2

....and this should then give a result from the query of

questionNum     responseNum   count   percent
1               1             1       20
1               2             3       60
1               3             1       20
2               1             2       66
2               2             1       33

I can do the query to get counts of each response but don't see a way to get percentages.

Could anyone help here please?

Many thanks

Upvotes: 1

Views: 250

Answers (1)

John Woo
John Woo

Reputation: 263733

SELECT a.questionNum, a.responseNum,
        COUNT(*) `count`,
        (COUNT(*) / b.totalCOunt) * 100 Percentage
FROM   table1 a
       INNER JOIN 
        (
          SELECT questionNum, COUNT(*) totalCOunt
          FROM table1
          GROUP BY questionNum
        ) b ON a.questionNUm = b.questionNum
GROUP BY questionNum, responseNum

you can also add additional function FLOOR

SELECT a.questionNum, a.responseNum,
        COUNT(*) `count`,
        FLOOR((COUNT(*) / b.totalCOunt) * 100) Percentage
FROM   table1 a
       INNER JOIN 
        (
          SELECT questionNum, COUNT(*) totalCOunt
          FROM table1
          GROUP BY questionNum
        ) b ON a.questionNUm = b.questionNum
GROUP BY questionNum, responseNum

Upvotes: 4

Related Questions