CODEWITHSUNDEEP
mysqlsql
PeakGen
PeakGen

Reputation: 23025

Counting how many times each unique element appeared in the table

I have a MySQL table called key_uniqueWord. This is how it looks like.

enter image description here

This is how the table is created..

CREATE TABLE `key_uniqueword` (
 `p_id` int(11) NOT NULL,
 `word` varchar(45) NOT NULL,
 `uniqueWordCount` int(11) NOT NULL,
 KEY `conv4` (`uniqueWordCount`,`p_id`,`word`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I can get the list of each unique individual record in word column from the following command.

SELECT DISTINCT `word` from `key_uniqueword`

I can check how many times a "particular" individual element in word column appeared in the key_uniqueword table by using the below command. For an example, lets check for the word "about"

SELECT COUNT(`word`) FROM `key_uniqueword` WHERE `word`="about"

Just like this, I need to know how many times "each" unique element in word column appears in the key_uniquword table. I am expecting the output to be like the below table structure. It has 2 columns, word and count

word   |   count
_ _ _ _ _ _ _ _ _ 
about  |   99,450
a      |   1000
talk   |   7980
men    |   20,915

How can I do this in a MySQL query? Important Please note that p_id not same for every row.

Upvotes: 0

Views: 1074

Answers (4)

Aman Aggarwal
Aman Aggarwal

Reputation: 18449

Correct query is:

SELECT COUNT(word) AS count FROM key_uniqueword group by word

Upvotes: 1

user2009750
user2009750

Reputation: 3187

SELECT word, count(*) as occurance FROM key_uniqueword GROUP BY word;

IMHO, if you are having third column where you store word count then it will be a very weak database design and will cause redundancy/duplication, my point is count in your case is a derived attribute. You shouldn't waste you storage for this.

In some cases its good to keep derived attributes in your database to speed up your queries but I think you don't need it here.

Upvotes: 1

baliman
baliman

Reputation: 620

select word, count(*) as count
from key_uniqueword
group by word

Upvotes: 2

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

select word, count(1)
from key_uniqueword
group by word;

Upvotes: 1

Related Questions