SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    More searched words Query !

    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.

    Code MySQL:
    --
    -- 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');
    Last edited by hiddenpearls; Jan 18, 2010 at 01:51. Reason: wrong writter table name

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Why is a column named tblWords?

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

  3. #3
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Why is a column named tblWords?

    Code:
    SELECT 
      tblWords, 
      COUNT(*)
    FROM 
      words 
    GROUP BY 
      tblWords 
    ORDER BY 
      COUNT(*) DESC
    oops , It's wrong written here. It is just 'word'

  4. #4
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @Dan Grossman
    I just edited that my table structure !

  5. #5
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @Dan Grossman
    Please rewrite the query, It says , #1111 - Invalid use of group function

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No, I gave you the correct query.

    Code:
    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. #7
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    No, I gave you the correct query.

    Code:
    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)
    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

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •