A liitle help on best method for sql polling..

[hotwire]

Hi Forum,

 

I know this could be a lengthy question posed, but Ima try to keep it as simple as possible  ..

 

I am trying to find the best method using mysql ver 4.3.4  to calculate the most entries in a column.

The columns (Polling Answers) consists of text strings such as baseball, ice cream, houston, etc

as the poll answers.

 

Is there a way, or what is the best mysql function to use in order to find the most (max) text

inputs of a column?

 

In other words, in poll question 1, I ask what is your favorite city? The user selects 1 out of 4

cities,  the cities are stored in mysql table as column pq01.  I need a formula that will tell me

what cities in column pq01 was selected the most.

 

Im not sure if this is possible using mysql like this.. but if not, then I need to do the calculations

for the most selected another way for polling..

 

Thanks for reading.. Any helpful information on this is much appreciated..

 

[fenway]

First, there is no mysql v4.3.4.

 

As for your question, if you wanted to do it one question a time, you could do the following (guessing DB structure here):

 

select question_id, count(answer) as cnt from pq01 where question_id = X and user_id = Y group by question_id order by cnt desc limit 1

 

But your table name suggests you've made one table per question?

[hotwire]

Hi fenway,

 

yes, the version appears to be 4.1 from the server.. please excuse that..

 

Actually, I have each question as a column with the answers...  column 5 (pq01), column 6 (pq02), etc..

pq meaning Poll Question..  So yes, I am attempting to query the most count for a given answer in each

column.  Another question I ponder is, if there is an equal count of answers in a given column, which

answer would be displayed as the (max) or most queried?  thx for your response..

[fenway]

Each question as a column really isn't ideal... not normalized table design.

 

As for the "tie", it's pretty much random.  If you want to deal with this boundary case, it's more complicated.

[hotwire]

Thanks for the info fenway,

 

So is that suggesting that I should have each question as its own table or

somehow assumed as rows? 

 

Or maybe you're stating that I'm using the wrong method of processing

the users answers.. Thx for your feedback..

[fenway]

Each question should be a record in a questions table.