当前位置: 动力学知识库 > 问答 > 编程问答 >

Querying MySQL database for unique counts of column values

问题描述:

I have a table structure that is similar to the following:

Table: poll_answers

----------------------------------------------------------------

| LONG id | LONG question_id | LONG user_id | TEXT answer |

----------------------------------------------------------------

Users' answers to a poll gets stored into the database as such. poll_answer is a string and many answers by different users can be the same.

I would like to query the database to list the number of each unique poll_answer.

Example of a poll question with id 123 may be:

What month was xyz born in?

The database might look like this:

----------------------------------------------------------------

| LONG id | LONG question_id | LONG user_id | TEXT answer |

----------------------------------------------------------------

| 0 | 123 | 1000 | monday |

| 1 | 123 | 1001 | tuesday |

| 2 | 123 | 1003 | monday |

| 3 | 123 | 1002 | monday |

| 4 | 123 | 1004 | thursday |

| 5 | 123 | 1025 | monday |

----------------------------------------------------------------

How can I query the database such that I get a list like this:

monday => 4

tuesday => 1

thursday => 1

The only one I can think of uses n number of queries where n is the number of different possible poll_answer.

SELECT UNIQUE answer FROM poll_answers

For each of the result,

SELECT COUNT(*) FROM poll_answers where answer = {the_answer}

Is there a more efficient way?

EDIT:

As a bonus, I would also like to know if efficiency-wise, it would be a good practice to generate and store a hash (MD5) of answer in the table and group by the hash instead.

网友答案:

If you need to get answers for ALL questions then try to use this query. It also orders answers for each query by popularity :

SELECT question_id,
       answer,
       COUNT(*) as answerCount
   FROM poll_answers
   GROUP BY question_id, answer 
   ORDER BY question_id, answerCount DESC

SQLFiddle demo

网友答案:

try this:

SELECT answer,COUNT(*) as uniqans FROM poll_answers group by answer
分享给朋友:
您可能感兴趣的文章:
随机阅读: