[SOLVED] Query Help

[idire]

I'm not much good with php mysql queries with joins etc.

 

Here is my problem.

 

I have stored the results of my questionnaire in a table. The answers are multiple choice and have between 2 and 6 answers.

I need a query that returns the number of answers of each and the total number of answers.

 

I could find the number of people with each answer in a sepearate query for each answer:

 

SELECT Count(*) FROM answers

SELECT Count(*) FROM answers WHERE A1 = '100'

SELECT Count(*) FROM answers WHERE A1 = '75'

SELECT Count(*) FROM answers WHERE A1 = '50'

SELECT Count(*) FROM answers WHERE A1 = '0'

 

Then would need to repeat for A2, A3, B1, B2 etc each with different possible answers

 

How could I make that into one query for each question? Any ideas?

[VBAssassin]

Don't ever use COUNT(*)!!! It pretty much forces table scans! Not good.

 

Anyway...

 

SELECT COUNT('A1') FROM `answers` WHERE `A1` = '50' OR `A1` = '75' OR `A1` = '100';

 

Kind regards,

Scott

 

P.S. You need an index on the A1 field.

[idire]

Wont that return a total number of people who answered?

 

I need seperatly the number of people who answered 100, the number of people who answered 75 etc

 

and thanks for tip about the *

[VBAssassin]

Wont that return a total number of people who answered?

 

I need seperatly the number of people who answered 100, the number of people who answered 75 etc

 

and thanks for tip about the *

 

Haha, you never said that (or did you?)... anyway... thats what GROUP BY is for ;-)

 

SELECT COUNT('A1') FROM `answers` WHERE `A1` = '50' OR `A1` = '75' OR `A1` = '100' GROUP BY `A1`;

 

Try that.

 

Kind regards,

Scott

[idire]

Wont that return a total number of people who answered?

 

I need seperatly the number of people who answered 100, the number of people who answered 75 etc

 

and thanks for tip about the *

 

Haha, you never said that (or did you?)... anyway... thats what GROUP BY is for ;-)

 

SELECT COUNT('A1') FROM `answers` WHERE `A1` = '50' OR `A1` = '75' OR `A1` = '100' GROUP BY `A1`;

 

Try that.

 

Kind regards,

Scott

 

I said the number of answers of each, but i guess I wasnt very clear

 

I tried that, but I dont think it will work as:

 

it doesnt return empty rows for the options where answer to count = 0 and it doesnt label them.

 

e.g.:

SELECT COUNT('A1') FROM `answer` WHERE `A1` = '50' OR `A1` = '75' OR `A1` = '100' GROUP BY A1

gives: 

COUNT( 'A1' )
2
2

[VBAssassin]

Wont that return a total number of people who answered?

 

I need seperatly the number of people who answered 100, the number of people who answered 75 etc

 

and thanks for tip about the *

 

Haha, you never said that (or did you?)... anyway... thats what GROUP BY is for ;-)

 

SELECT COUNT('A1') FROM `answers` WHERE `A1` = '50' OR `A1` = '75' OR `A1` = '100' GROUP BY `A1`;

 

Try that.

 

Kind regards,

Scott

 

I said the number of answers of each, but i guess I wasnt very clear

 

I tried that, but I dont think it will work as:

 

it doesnt return empty rows for the options where answer to count = 0 and it doesnt label them.

 

e.g.:

SELECT COUNT('A1') FROM `answer` WHERE `A1` = '50' OR `A1` = '75' OR `A1` = '100' GROUP BY A1

gives: 

COUNT( 'A1' )
2
2

 

Oh... this will return data showing which ones have X amount

 

SELECT `A1`, COUNT('A1') FROM `answer` WHERE `A1` = '50' OR `A1` = '75' OR `A1` = '100' GROUP BY `A1`;

 

If it don't show results with 0, then just make a php script to sort that out ;-)

 

Kind regards,

Scott

[idire]

Thank you, that completely sorts the problem, dont know why i didnt think of it.

 

Thanks!