Optimize an SQL query to get count of specific answers

Question

I'm creating a survey with 10 questions. All questions have 5 possible answers with values from 1-5. The data is stored in a database with one row per user. There is a column for the answer to every question.

To make bar graphs for the answers to every question, I currently retrieve the count of rows where the value of a specific column is equal to a specific possible answer:

SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage1` = 1
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage1` = 2
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage1` = 3
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage1` = 4
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage1` = 5
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage2` = 1
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage2` = 2
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage2` = 3
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage2` = 4
SELECT COUNT(*) AS `records_found` FROM (`antworten`) WHERE `frage2` = 5
(...)

This will generate a graph like this: http://i.imgur.com/SESJ8.png

This is probably very stupid, and there is probably a much better way to retrieve the desired data. I just can't come up with it, could someone help me? :) Thank you.

Answer 1

For a single question you can use

select  frage1, count(*) as `records_found`
  from  antworten
group by frage1
order by frage1

or similar. There's probably a way to do all questions at once using cubes and pivots etc. but I don't know it.

Answer 2

Maybe:

SELECT 1 As FrageNummer, frage1 As Frage, count(*) As Anzahl
FROM antworten
GROUP BY frage1
UNION
SELECT 2 As FrageNummer, frage2 As Frage, count(*) As Anzahl
FROM antworten
GROUP BY frage2

And so on.

It would of course be easier to query, if the 'fragen' were put into rows instead of columns, i.e. having data like this:

id | quartalid | frage_nr | frage
---------------------------------
9  |         5 |        1 |     5
9  |         5 |        2 |     5
9  |         5 |        3 |     2

etc.

Then you could query simply like this

SELECT frage_nr, frage, count(*)
FROM antworten
GROUP BY frage_nr, frage