Optimize an SQL query to get count of specific answers
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 `re开发者_运维百科cords_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.
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
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.
精彩评论