开发者

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.

Optimize an SQL query to get count of specific answers

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜