Returning all distinct entries from table

[macleodjb]

This should be an easy answer for you professionals out there. What i am trying to do is return all distinct values from a table that i have defined. The table is used as a poll kind of.

I found this on mysql.dev "count(DISTINCT)" but i'm not sure how it's used in a query.

I tried this...

Select count(DISTINCT) FROM myTable
Select *, count(DISTINCT) AS Poll FROM myTable

Any help is appreciated.

Thanks in advance.

[abduraooft]

Use group by (http://http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html)

[macleodjb]

I'm sorry but can you write the proper syntax for this for me.

[abduraooft]

I'm sorry but can you write the proper syntax for this for me.
For that I need to see your table schema.

You may modify select filed_name from table_name group_by filed_name

[macleodjb]

I dont have one set up yet. I wanted to make sure it could be done prior to writing the code. let me explain what i'm trying to do a little better.

lets say I have a poll with 5 questions. and in my table each field will record the answer for each question. Thus i will have 5 fields in my table.

Each answer can be as follows...

No = 0
Yes = 1
Maybe = 3
Never = 4

so now i will have a different answer within each 5 field for different entries. depending on the answer 0-4.

My goal is to return the number of times users have selected no, yes, maybe, and never. And it should be across the entire table not per field. So for example i want to return a number of times the value of 1 occurs in the entire table and records, then i will break it up later between the individual questions.

I know it sounds weird but i want to spit out a percentage across the board.

I hope that makes sense.

[Old Pedant]

So you need a better DB design.

If you have a poll with ANY NUMBER of questions, you should have a *SEPARATE* table for the actual answers.

For example:

TABLE: answeredBy
id : int auto_number primary key
name : varchar (who answered)
email : varchar
... etc. ... (whatever you want to remember about the person who answered)

TABLE: answers
id : int foreign key to answeredBy table
qid : int (should be foreign key to the pollQuestions table)
answer: int (no=0, yes=1, etc.)

TABLE: pollQuestions
qid : int auto_number primary key
question : varchar


This is called "NORMALIZATION". And now the problem becomes trivial:

To find the count of all YES answers, no matter which question:

SELECT COUNT(*) FROM answers WHERE answer = 1;

To find the number of people who answered YES to two or more questions:

SELECT COUNT(A1.*)
FROM answers AS A1, answers AS A2
WHERE A1.id = A2.id
AND A1.answer = 1
AND A2.answer = 1
AND A1.questionNumber <> A2.questionNumber

And so on... Many many queries now become not only possible by actually pretty easy, thanks to good DB design.

Notice another huge advantage of this scheme: You could run another poll by just adding more questions to the pollQuestions table and you would need *NO CHANGES* to your other tables.