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)
<Edited by 123finder.com on 12-16-2000 at 08:19 PM>
Have you tried a GROUP BY clause?
SELECT *, COUNT(dupeColumn) FROM tblName GROUP BY dupeColumn
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?
I am no MySQL guru, but should there not be a * after the select statement?
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?
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()
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
It might be the version I am using 3.23.23 also would this query be used in conjunction with fetching any other data?
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!
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.