More SQL noobness

seamus

Right, the queries I've been using to achieve this seem ridiculously overcomplex, and I'm sure the correct answer will give me a function I should have been using a long time ago...

I have a survey, where all the results are stored in a table. For each question, there are four possible answers: -2, -1, 1, or 2 - stored as an INT in the table.

Now, I want to get a count of how many people gave each answer, for each question. So for q1, I want to get how many people answered -2, how many answered -1, etc.

Currently, I'm using the following query for question 1:

SELECT COUNT(q1) as num FROM survey WHERE q1 = 2 AND survey_date BETWEEN 1122850800 AND 1123455599
UNION 
SELECT COUNT(q1) as num FROM survey WHERE q1 = 1 AND survey_date BETWEEN 1122850800 AND 1123455599 
UNION 
SELECT COUNT(q1) as num FROM survey WHERE q1 = -1 AND survey_date BETWEEN 1122850800 AND 1123455599 
UNION
SELECT COUNT(q1) as num FROM survey WHERE q1 = -2 AND survey_date BETWEEN 1122850800 AND 1123455599

Which, sometimes gives a result like

num
 10
 15
  6
  0

But if there's only one response in that time period, gives a result like

 num
  0
  1

So I assume it's only returning each distinct count() value once. I need to it to return 4 values for the question, even if 3 of them are the same.

Any ideas?

BC

If i'm understanding you correctly you need to use a GROUP BY clause. I'm not sure what your table structure is like but you should be able to do something like:

select question, answer, count(answer)
from tablename
group by question, answer

Should give you something like:

Question Answer Count
Q1 -1 4
Q1 -2 4
Q1 2 4
Q1 1 4

seamus

Cheers, but the problem is that question (in your example) is a column name, not a row value. Each row stores an entire survey as opposed to a single answer (you have columns for each different question).

I've come up with a workaround, by creating a second table containing the four possible answers, and joining on survey.q1 = surveyval.id. It's a smaller query, but obviously requires the second table.

If anyone else has a more efficient answer, I'd be glad to hear it.

amen

post your table structure and few lines of sample data

Inspector Gadget

Seamus:

It's been a while since I've actually done this myself, and have subsequently (of course) forgotten the details of this, but maybe a crosstab query might help?

[edit]Possibly Useful Link[/edit]

Gadget

bonkey

seamus wrote:

I've come up with a workaround, by creating a second table containing the four possible answers, and joining on survey.q1 = surveyval.id. It's a smaller query, but obviously requires the second table.

If anyone else has a more efficient answer, I'd be glad to hear it.

I don't think there is one Seamus. You have N questions, each of which can have 4 answers. Therefore, you want 4 rows of N values as your answer. Without changing your database structure, you can either UNION 4 queries as you did in your first solution, or you can join a table which contains the valid answers.

Unfortunately, you're gonna have to join it to *each* of the question columns.

Personally, I don't like the database structure, and its going to make life unnecessarily difficult either which way. Ideally, yiou'd store Question_Number as a field value, as well as Question_Answer. Link to a Questions table, and an Answers table, and you can easily manage the number of questions, the number of answers, etc. With a tiny bit more work, this structure would also allow you to define questions with different answer ranges etc.

Is table redesign out of the question?

jc

seamus

Cheers Guys,
I think bonkey managed to unravel it in my head too.....

bonkey wrote:

I don't think there is one Seamus. You have N questions, each of which can have 4 answers. Therefore, you want 4 rows of N values as your answer. Without changing your database structure, you can either UNION 4 queries as you did in your first solution, or you can join a table which contains the valid answers.

Unfortunately, you're gonna have to join it to *each* of the question columns.

Yep. Not that big a deal, as there are only three questions, so three queries. But ultimately, as you point out below, it should be possible, in one query to get list of questions, and the number of answers for each possible answer - if the database was designed correctly.

Personally, I don't like the database structure, and its going to make life unnecessarily difficult either which way. Ideally, yiou'd store Question_Number as a field value, as well as Question_Answer. Link to a Questions table, and an Answers table, and you can easily manage the number of questions, the number of answers, etc. With a tiny bit more work, this structure would also allow you to define questions with different answer ranges etc.

I think I get what you're getting at. I designed it with a single survey in mind, with a fixed number of questions and possible answers but best practice may be to make it flexible - which would also give me a more flexible reporting/querying ability on it too.

Is table redesign out of the question?

Not really. The frontend is PHP, so in theory even a massive DB redesign shouldn't affect the PHP code, all I'd need to do is create new queries to give the PHP the data it's looking for.

As I type, my initial thoughts are:
4 tables:
Survey table, containing the other details that need to be stored.
Questions table, containing the questions to be asked
Answers table, containing the possible answers (all questions have the same
possible answers, but this table may actually come in useful for another issue we've bumped into)
Response table - containing Survey/Question/Answer triples

I'll mess about with it, and let ye know what I come up with.

Thanks for the input.