SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    In MySQL, we could use DISTINCT to choose not to display duplicates and only keep one row of these duplications.
    However, now I need to know how many times these rows are duplicated. How would I do it with MySQL SELECT?

    Add another question:
    Why couldn't I use this statement (it is used in MySQL documentation)

    select COUNT(DISTINCT results) from student;

    It said syntax error near DISTINCT result)

    Thanks


    <Edited by 123finder.com on 12-16-2000 at 08:19 PM>
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Have you tried a GROUP BY clause?

    Code:
    SELECT *, COUNT(dupeColumn) FROM tblName GROUP BY dupeColumn
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Terrific! I didn't know that I could do that much stuff with MySQL before PHP get the handle!

    About the other question, do you know why I execute:

    select COUNT(DISTINCT field) from myTb;

    And got a syntax error?
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  4. #4
    SitePoint Evangelist mad-onion's Avatar
    Join Date
    Aug 2000
    Location
    Land of the long white cloud
    Posts
    556
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am no MySQL guru, but should there not be a * after the select statement?
    SiteOptions >> Services :: Products :: Contact
    Developers of PHP, C++, Visual Basic, MySQL, and more!

  5. #5
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    No there shouldn't. Despite this, nothing in my MySQL reference seems to indicate that DISTINCT may be used to modify an argument to COUNT()... Where in the MySQL docs did you see this done?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  6. #6
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is where i saw it:

    http://www.mysql.com/documentation/m...p_by_functions

    Near the top. Thanks
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I got it to work I my database, that's weird although it just returned the total distinct items from a column in my case I have a City column when I use
    SELECT COUNT(DISTINCT City) from listings;

    It gies me 26, and Icould have gotten that with mysql_num_rows()
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But would it more efficient to let MySQL process the result instead of receiving the result set and process it using mysql_num_rows() in PHP? Number of rows I receive could well over several thousands.

    So do you think the version of MySQL matters? Mine: 3.22.32
    Thanks
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It might be the version I am using 3.23.23 also would this query be used in conjunction with fetching any other data?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nah, I only want to know how many fields are duplicated.
    Actually I currently implement it using mysql_num_rows(), i'll try to find some documents about version issue then!
    Thanks a lot everyone!
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would say it is much better to do it in the query if that is all the query will be doing than to run the query and then use mysql_num_rows.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •