CODEWITHSUNDEEP
mysqlcount
Danilo Bargen
Danilo Bargen

Reputation: 19432

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.

Data table

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: https://i.sstatic.net/zZXCg.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.

Upvotes: 0

Views: 254

Answers (2)

Janick Bernet
Janick Bernet

Reputation: 21184

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

Upvotes: 4

Rup
Rup

Reputation: 34408

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.

Upvotes: 0

Related Questions