More searched words Query!

Databases
1

hi,

I am trying to make a sql query which will fetch the more searched words from database table. table name is tblWords

I am pasting my Table structure here.

--
-- Table structure for table `tblWords`
--

CREATE TABLE IF NOT EXISTS `tblWords` (
  `id` int(99) NOT NULL default '0',
  `word` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblWords`
--

INSERT INTO `tblWords` (`id`, `word`) VALUES
(0, 'final'),
(1, 'final'),
(2, 'test'),
(3, 'test'),
(4, 'final'),
(5, 'final'),
(6, 'sample');
2

Why is a column named tblWords?

SELECT 
  tblWords, 
  COUNT(*)
FROM 
  words 
GROUP BY 
  tblWords 
ORDER BY 
  COUNT(*) DESC
3

oops , It’s wrong written here. It is just ‘word’

4

@Dan Grossman
I just edited that my table structure !

5

@Dan Grossman
Please rewrite the query, It says , #1111 - Invalid use of group function

6

No, I gave you the correct query.

mysql> 
SELECT 
  word, 
  COUNT(*) 
FROM 
  tblWords 
GROUP BY 
  word 
ORDER BY 
  COUNT(*) DESC;

+--------+----------+
| word   | COUNT(*) |
+--------+----------+
| final  |        4 |
| test   |        2 |
| sample |        1 |
+--------+----------+
3 rows in set (0.00 sec)
7

Your Query seems right, I think there is a conflict in yours MYSQL server and my Live MYSQL server. I used this Query and then it worked fine

SELECT word,count(*) as total FROM `tblwords` Group by word order by total desc