|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Codewalkers Forums Sponsor:
|
|
|
|
#1
April 11th, 2004, 04:19 AM
|
|||
|
|||
|
COUNT DISTINCT inaccurate
I have a mysql database containing a list of registrants for our software. The table is named 'registration' and the country field is called 'country'
I am trying to get a count of the number of distinct countries from which people have registered. This code seems to work, but the number is not exactly accurate. Any ideas? It returns a value of 25 yet there are 27 distinct countries in the table Code:
SELECT COUNT( DISTINCT country ) FROM registration To clarify, below is a list generated using the following code - note the 27 distinct countries that this code generated Code:
SELECT country, COUNT( * ) AS cat_num FROM registration GROUP BY country Australia = 29 Austria = 1 Azerbaijan = 1 Brazil = 1 Canada = 4 Colombia = 1 Finland = 1 Germany = 5 Hungary = 1 India = 1 Indonesia = 1 Italy = 3 Japan = 2 Mexico = 2 Micronesia = 1 Netherlands = 1 New Caledonia = 1 New Zealand = 1 Panama = 1 Philippines = 1 Portugal = 1 Russian Fed. = 3 Switzerland = 1 Taiwan = 1 Thailand = 1 United Kingdom = 3 United States = 62 Really appreciate any help trying to troubleshoot this one. Thanks, Adrian
|
|
#2
April 11th, 2004, 01:43 PM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
I imported the list of countries into a db and ran the distinct count query and got a result of 27...I am sure that does not help, but figured I would let you know. In order to fully troubleshoot, I would need an exact copy of the table and data in it. If you can provide that info (I don't know if the info is private) let me know.
|
|
#3
April 12th, 2004, 06:13 PM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
Blindeddie,
Thanks for the feedback on that - makes me think that maybe there is a glitch in my table structure somewhere. This is my first experience with sql and php so there is a good chance I messed something up. Here is the code for the table. I'd rather not post the data for obvious reasons. Data has been entered into the table via PHP form submissions and also some of the older data was entered manually via PHPMYADMIN. Let me know if this helps troubleshoot at all or whether you will need the data. Thanks again, Adrian Code:
CREATE TABLE `registration` (
`sender_last_name` varchar(30) NOT NULL default '--Last Name--',
`sender_first_name` varchar(30) NOT NULL default '--First Name--',
`sender_email` varchar(150) NOT NULL default '--Email--',
`job` enum('Choose One','Interested Public','Community Activist','Resource Manager','Graphics Professional','Scientist','University Professor','Student (K-8)','Student (9-12)','Student (Undergraduate)','Student (Graduate)','Teacher (K-8)','Teacher (9-12)','Local Agency','State Agency','Federal Agency','Other') NOT NULL default 'Choose One',
`job_other` varchar(150) NOT NULL default '--List Other Here--',
`company` varchar(100) NOT NULL default '--Enter Company Name--',
`country` enum('Select Country','Afghanistan','Albania','Algeria','Amer.V irgin Is.','Andorra','Angola','Anguilla','Antarctica','A ntigua/Barbads','Argentina','Armenia','Aruba','Australia' ,'Austria','Azerbaijan','Bahamas','Bahrain','Bangl adesh','Barbados','Belarus','Belgium','Belize','Be nin','Bermuda','Bhutan','Bolivia','Bosnia-Herz.','Botswana','Bouvet Island','Brazil','Brit.Ind.Oc.Ter','Brit.Virgin Is.','Brunei Dar-es-S','Bulgaria','Burkina-Faso','Burkina-Faso','Burundi','Cambodia','Cameroon','Canada','Ca pe Verde','Cayman Islands','Central Afr.Rep','Chad','Channel Islands','Chile','China','Christmas Islnd','Coconut Islands','Colombia','Comoro','Congo','Cook Islands','Costa Rica','Croatia','Cuba','Cyprus','Czech Republic','Denmark','Djibouti','Dominica','Dominic an Rep.','Ecuador','Egypt','El Salvador','Equatorial Guin','Eritrea','Estonia','Ethiopia','Faeroe Islands','Falkland Islnds','Fiji','Finland','France','Frenc.Polynesia ','French Guiana','Gabon','Gambia','Georgia','Germany','Ghan a','Gibraltar','Greece','Greenland','Grenada','Gua deloupe','Guam','Guatemala','Guinea','Guinea-Bissau','Guyana','Haiti','Heard/McDon.Isl','Honduras','Hong Kong','Hungary','Iceland','India','Indonesia','Ira n','Iraq','Ireland','Israel','Italy','Ivory Coast','Jamaica','Japan','Jordan','Kazakhstan','Ke nya','Kirghistan','Kiribati','Kuwait','Laos','Latv ia','Lebanon','Lesotho','Liberia','Libya','Liechte nstein','Lithuania','Luxembourg','Macau','Macedoni a','Madagascar','Malawi','Malaysia','Maldives','Ma li','Malta','Marshall Islnds','Martinique','Mauritania','Mauritius','May otte','Mexico','Micronesia','Minor Outl.Isl.','Moldavia','Monaco','Mongolia','Montser rat','Morocco','Mozambique','Myanmar','N.Mariana Islnd','Namibia','Nauru','Nepal','Neth. Antilles','Netherlands','New Caledonia','New Zealand','Nicaragua','Niger','Nigeria','Niue Islands','Norfolk Island','North Korea','Norway','Oman','Pakistan','Palau','Panama' ,'Pap. New Guinea','Paraguay','Peru','Philippines','Pitcairn Islnds','Poland','Portugal','Puerto Rico','Qatar','Reunion','Romania','Russian Fed.','Rwanda','S.Tome,Principe','Samoa,American', 'San Marino','Saudi Arabia','Senegal','Seychelles','Sierra Leone','Singapore','Slovakia','Slovenia','Solomon Islands','Somalia','South Africa','South Korea','Spain','Sri Lanka','St. Helena','St. Lucia','St. Vincent','St.Kitts,Nevis','St.Pier,Miquel.','Sudan ','Suriname','Svalbard','Swaziland','Sweden','Swit zerland','Syria','Tadzhikistan','Taiwan','Tanzania ','Thailand','Togo','Tokelau Islands','Tonga','Trinidad,Tobago','Tunisia','Turk ey','Turkmenistan','Turks and Caicos','Tuvalu','Uganda','Ukraine','United Kingdom','United States','Uruguay','Utd.Arab.Emir.','Uzbekistan','V anuatu','Vatican City','Venezuela','Vietnam','Wallis,Futuna','West Sahara','Western Samoa','Yemen','Yugoslavia','Zaire','Zambia','Zimb abwe') NOT NULL default 'Select Country',
`state` enum('Select State','AL','AK','AZ','AR','CA','CO','CT','DE','DC ','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA ','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV ','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA ','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV ','WI','WY') NOT NULL default 'Select State',
`message` text NOT NULL,
`platform` enum('Choose One','WINDOWS','MAC') NOT NULL default 'Choose One',
`illustrator` enum('Choose One','10','CS') NOT NULL default 'Choose One',
`referrer` enum('Choose One','Search Engine','Email from IAN','Browsing IAN Website','IAN Conceptual Diagrams Newsletter','Adobe Studio Exchange','Adobe User Forums','Other Web Forum','Avenza MAPublisher','Personal Referral','Other') NOT NULL default 'Choose One',
`referrer_other` varchar(150) NOT NULL default '--List Other Here--',
`friend_name` varchar(100) NOT NULL default '--Name--',
`friend_email` varchar(150) NOT NULL default '--Email--',
`install_type` enum('New','Upgrade') NOT NULL default 'New',
`version_number` enum('3.2','4.1') NOT NULL default '3.2',
`date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`sender_email`),
UNIQUE KEY `sender_email_2` (`sender_email`),
KEY `country` (`country`),
FULLTEXT KEY `sender_email` (`sender_email`),
FULLTEXT KEY `job_other` (`job_other`),
FULLTEXT KEY `company` (`company`),
FULLTEXT KEY `message` (`message`),
FULLTEXT KEY `referrer_other` (`referrer_other`),
FULLTEXT KEY `sender_first_name` (`sender_first_name`),
FULLTEXT KEY `sender_last_name` (`sender_last_name`),
FULLTEXT KEY `friend_name` (`friend_name`)
) TYPE=MyISAM;
|
|
#4
April 12th, 2004, 08:51 PM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
Without the actual data, it would be extremly difficult to troubleshoot
your problem. The table structure seems fine, so I am not sure about what is going on.
|
|
#5
April 12th, 2004, 10:10 PM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
OK Here goes - this is most of the table, just without the name, email and message fields. I have also exported the complete table structure using PHPMYADMIN and you can see it here:
http://ian.umces.edu/table_structure.htm Hope this helps to clarify, Adrian Code:
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES', 'United States', 'MD', 'WINDOWS', 'CS', 'Personal Referral', '--List Other Here--', 'Upgrade', '4.1', '2004-03-15');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES', 'United States', 'MD', 'WINDOWS', 'CS', 'Personal Referral', '--List Other Here--', 'Upgrade', '4.1', '2004-03-15');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'University of Maryland Center for Environmental Science', 'United States', 'MD', 'WINDOWS', 'CS', 'Other', 'My Idea', 'Upgrade', '4.1', '2004-03-15');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES', 'United States', 'MD', 'WINDOWS', 'CS', 'Personal Referral', '--List Other Here--', 'Upgrade', '4.1', '2004-03-15');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'Hokkiado University', 'Japan', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-02-27');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'Graduate University for Advanced Studies', 'Japan', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-02-27');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'Horn Point Lab', 'United States', 'MD', 'WINDOWS', '10', 'Email from IAN', '--List Other Here--', 'New', '3.2', '2003-02-28');
INSERT INTO `registration` VALUES ('Other', 'State/federal agency', 'Sea Grant', 'United States', 'MD', 'Choose One', '10', 'Choose One', '--List Other rnrnHere--', 'New', '3.2', '2003-02-02');
INSERT INTO `registration` VALUES ('Graphics Professional', '--List Other Here--', 'Marine Botany', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '--List Other rnrnHere--', 'New', '3.2', '2003-03-03');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'NASA/GSFC Wallops Flight Facility', 'United States', 'VA', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-05');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'UMCES', 'United States', 'MD', 'WINDOWS', '10', 'Email from IAN', '--List Other Here--', 'New', '3.2', '2003-03-08');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'UTS', 'Australia', '', 'WINDOWS', '10', 'Email from IAN', '--List Other Here--', 'New', '3.2', '2003-03-09');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Fisheries Centre, UBC', 'Canada', '', 'Choose One', '10', 'Choose One', '--List rnrnOther Here--', 'New', '3.2', '2003-03-09');
INSERT INTO `registration` VALUES ('State Agency', '--List Other Here--', 'Delaware DNREC', 'United States', 'DE', '', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-10');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'UFRPE', 'Brazil', '', 'Choose One', '10', 'Personal Referral', '--List Other rnrnHere--', 'Upgrade', '4.1', '2004-03-20');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Delaware DNREC', 'United States', 'DE', 'Choose One', 'Choose One', '', '--List Other Here--', 'New', '3.2', '2003-03-10');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES', 'United States', 'MD', 'WINDOWS', 'CS', 'Personal Referral', '--List Other Here--', 'Upgrade', '4.1', '2004-03-15');
INSERT INTO `registration` VALUES ('Graphics Professional', 'Designer', '--Enter Company Name--', 'Mexico', '', 'WINDOWS', '10', 'Adobe Studio Exchange', '--List Other Here--', 'New', '4.1', '2004-04-09');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', '--Enter Company Name--', 'United States', 'TX', 'WINDOWS', 'CS', 'Adobe User Forums', '', 'New', '4.1', '2004-04-10');
INSERT INTO `registration` VALUES ('Graphics Professional', '--List Other Here--', '--Enter Company Name--', 'United States', 'OH', 'WINDOWS', 'CS', 'Adobe User Forums', '--List Other Here--', 'New', '4.1', '2004-04-10');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '--List Other Here--', '--Enter Company Name--', 'Portugal', '', 'WINDOWS', 'CS', 'Search Engine', '--List Other Here--', 'New', '4.1', '2004-04-10');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '--List Other Here--', 'UW-Madison', 'United States', 'WI', 'WINDOWS', 'CS', 'Adobe User Forums', '--List Other Here--', 'New', '4.1', '2004-04-10');
INSERT INTO `registration` VALUES ('Other', 'Engineer', 'personal', 'India', '', 'WINDOWS', '10', 'Adobe Studio Exchange', '--List Other Here--', 'New', '4.1', '2004-04-10');
INSERT INTO `registration` VALUES ('Graphics Professional', '--List Other Here--', '--Enter Company Name--', 'United States', 'NY', 'WINDOWS', '10', 'Other Web Forum', 'Adobe web site', 'New', '4.1', '2004-04-10');
INSERT INTO `registration` VALUES ('State Agency', '--List Other Here--', 'DE DNREC', 'United States', 'DE', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-10');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'University of New Orleans', 'United States', 'LA', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-10');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES', 'United States', 'MD', 'WINDOWS', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-03-10');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'University of Alaska Fairbanks', 'United States', 'AK', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-10');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Cornell Cooperative Extension, Marine Program', 'United States', 'NY', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-11');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'VIMS', 'United States', 'VA', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-11');
INSERT INTO `registration` VALUES ('State Agency', '--List Other Here--', 'VIMS', 'United States', 'VA', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-12');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES', 'United States', 'MD', 'WINDOWS', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-14');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', 'WBM', 'Australia', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-16');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'FTZ', 'Germany', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-17');
INSERT INTO `registration` VALUES ('Scientist', 'WBM', '--Enter Company Name--', 'Australia', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-17');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'University of Technology, Sydney', 'Australia', '', '', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-18');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES', 'United States', 'MD', 'WINDOWS', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-26');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'CICESE', 'Mexico', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-26');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'INSTITUTO DE INVESTIGACIONES MARINAS Y COSTERAS INVEMAR', 'Colombia', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-03-28');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Tropical Biology, James Cook University', 'Australia', '', 'MAC', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-03-29');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'CSIRO', 'Australia', '', 'WINDOWS', '10', '', '--List Other Here--', 'New', '3.2', '2003-03-30');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Everglades National Park', 'United States', 'FL', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-01');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'UMCES CBL', 'United States', 'MD', 'WINDOWS', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-01');
INSERT INTO `registration` VALUES ('Other', 'National Estuary Program', 'Maryland Coastal Bays Program', 'United States', 'MD', 'Choose One', 'Choose One', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-02');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'National Taiwan University', 'Taiwan', '', 'Choose One', 'Choose One', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-06');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'Horn Point Lab', 'United States', 'MD', 'WINDOWS', '10', '', '--List Other Here--', 'New', '3.2', '2003-04-07');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES Horn Point Lab', 'United States', 'MD', 'WINDOWS', '10', 'Email from IAN', '--List Other Here--', 'New', '3.2', '2003-04-08');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'University of the Philippines', 'Philippines', '', 'WINDOWS', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-08');
INSERT INTO `registration` VALUES ('State Agency', '--List Other Here--', 'Qld EPA', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-04-09');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Environmental Protection Agency', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-04-09');
INSERT INTO `registration` VALUES ('State Agency', '--List Other Here--', 'Environmental Protection Agency', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-04-10');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'University of Miami', 'United States', 'FL', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-11');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'University of Miami - RSMAS', 'United States', 'FL', 'Choose One', 'Choose One', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-04-11');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Marine Biological Laboratory', 'United States', 'MD', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-17');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'Asian Institute of Technology', 'Thailand', '', 'WINDOWS', '10', 'Search Engine', '--List Other Here--', 'Upgrade', '4.1', '2004-03-15');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', 'Stazione Zoologica 'A. Dohrn', Napoli', 'Italy', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-22');
INSERT INTO `registration` VALUES ('Resource Manager', 'Scientist', 'BALU UALA and CREA-Panama Foundation', 'Panama', '', 'Choose One', '10', 'Choose One', '--List Other Here--', 'New', '3.2', '2003-04-29');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Queensland Dept. Natural Resources & Mines', 'Australia', '', 'WINDOWS', '10', 'Choose One', '--List Other Here--', 'Upgrade', '4.1', '2004-03-15');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'UMCES Horn Point Lab', 'United States', 'MD', 'WINDOWS', '10', 'Personal Referral', '--List Other Here--', 'Upgrade', '4.1', '2004-03-15');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'United States', 'CO', 'Choose One', '10', 'Choose One', '--List Other rnrnHere--', 'New', '3.2', '2003-05-09');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'Stanford University', 'United States', 'CA', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2004-05-15');
INSERT INTO `registration` VALUES ('Other', '--List Other Here--', '--Enter Company Name--', 'United States', 'CA', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-05-18');
INSERT INTO `registration` VALUES ('Student (9-12)', '--List Other Here--', '--Enter Company Name--', 'United States', 'CA', 'Choose One', '10', 'Search Engine', '--List Other rnrnHere--', 'New', '3.2', '2003-05-18');
INSERT INTO `registration` VALUES ('Other', '--List Other Here--', '--Enter Company Name--', 'New Caledonia', '', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-05-21');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Murdoch University', 'Australia', '', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-05-27');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'Water Studies Centre, Monash Univ', 'Australia', '', 'Choose One', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-05-29');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '--List Other Here--', '--Enter Company Name--', 'Germany', '', 'Choose One', '10', 'Search Engine', '--List Other rnrnHere--', 'New', '3.2', '2003-05-30');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'Australia', '', 'Choose One', '10', 'Search Engine', '--List rnrnOther Here--', 'New', '3.2', '2003-06-01');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'United States', 'UT', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-02');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'Monash University', 'Australia', '', 'Choose One', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-06-02');
INSERT INTO `registration` VALUES ('State Agency', '--List Other Here--', 'Maryland DNR', 'United States', 'MD', 'Choose One', '10', '', '--List Other Here--', 'New', '3.2', '2003-06-03');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Sea Grant', 'United States', 'MD', 'Choose One', '10', 'Personal Referral', '--List Other rnrnHere--', 'New', '3.2', '2003-06-03');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '--List Other Here--', 'CBL', 'United States', 'MD', 'WINDOWS', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2003-06-04');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'NRCET', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '--List Other Here--', 'New', '3.2', '2004-06-04');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Graben', 'Austria', '', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-06');
INSERT INTO `registration` VALUES ('Resource Manager', '--List Other Here--', 'swiss sustainable systems ag', 'Switzerland', '', 'Choose One', '10', 'Search Engine', '--List rnrnOther Here--', 'New', '3.2', '2003-06-06');
INSERT INTO `registration` VALUES ('Other', 'Hobbyist', '--Enter Company Name--', 'United States', 'WI', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-10');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'Germany', '', 'WINDOWS', 'CS', 'Search Engine', '--List Other Here--', 'New', '4.1', '2004-04-10');
INSERT INTO `registration` VALUES ('Resource Manager', '--List Other Here--', 'CRN/RE', 'Russian Federation', 'Select State', 'WINDOWS', '10', 'Other Web Forum', 'http://www.graphics.ru', 'New', '4.1', '2004-04-09');
INSERT INTO `registration` VALUES ('Resource Manager', '--List Other Here--', 'Hermantech', 'Finland', '', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-10');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '--List Other Here--', '--Enter Company Name--', 'United States', 'OH', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-11');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '--List Other Here--', 'ANC', 'United States', 'NV', 'Choose One', '10', 'Search Engine', '--List rnrnOther Here--', 'New', '3.2', '2003-06-11');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'NIWA', 'New Zealand', '', '', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-16');
INSERT INTO `registration` VALUES ('Federal Agency', '--List Other Here--', 'Fast Friends', 'Azerbaijan', '', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-17');
INSERT INTO `registration` VALUES ('Federal Agency', '--List Other Here--', 'Smithsonian Institution', 'United States', 'WA', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-17');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', 'university of essen', 'Germany', '', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-17');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'PU', 'United States', 'NJ', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-17');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', 'DBD-', 'United States', 'WI', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-17');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', 'University of New South Wales', 'Australia', '', 'Choose One', '10', 'Search Engine', '--List Other Here--', 'New', '3.2', '2003-06-17');
INSERT INTO `registration` VALUES ('University Professor', '--List Other Here--', 'Oberlin College', 'United States', 'OH', 'Choose One', '10', 'Personal Referral', '--List rnrnOther Here--', 'New', '3.2', '2003-06-18');
INSERT INTO `registration` VALUES ('State Agency', '', 'Idaho DEQ', 'United States', 'ID', '', '', 'Search Engine', '', 'New', '3.2', '2003-06-19');
INSERT INTO `registration` VALUES ('Interested Public', '', 'Home Use', 'United Kingdom', '', '', '', 'Search Engine', '', 'New', '3.2', '2003-06-22');
INSERT INTO `registration` VALUES ('Student (Graduate)', '', 'Edith Cowan University', 'Australia', '', '', '', 'Personal Referral', '', 'New', '3.2', '2003-06-23');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '', 'Vandelay Enterprises', 'United States', 'OH', '', '', 'Search Engine', '', 'New', '3.2', '2004-06-23');
INSERT INTO `registration` VALUES ('Scientist', '', 'Edith Cowan University', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '', 'New', '3.2', '2003-06-24');
INSERT INTO `registration` VALUES ('University Professor', '', 'Edith Cowan University', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '', 'New', '3.2', '2003-06-24');
INSERT INTO `registration` VALUES ('Student (Graduate)', '', 'Edith Cowan University', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '', 'New', '3.2', '2003-06-24');
INSERT INTO `registration` VALUES ('Student (Graduate)', '', 'Cambridge University', 'United Kingdom', '', 'WINDOWS', '', 'Search Engine', '', 'New', '3.2', '2003-06-26');
INSERT INTO `registration` VALUES ('Other', '', 'Camelford Graham', 'Canada', '', '', '', 'Search Engine', '', 'New', '3.2', '2003-06-26');
INSERT INTO `registration` VALUES ('Scientist', '', 'Edith Cowan University', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '', 'New', '3.2', '2003-06-26');
INSERT INTO `registration` VALUES ('Interested Public', '', '', 'Canada', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-06-29');
INSERT INTO `registration` VALUES ('University Professor', '', 'UMCES', 'United States', 'MD', 'MAC', '10', 'Personal Referral', '', 'New', '3.2', '2003-06-29');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '', '', 'United States', 'MD', 'WINDOWS', '10', 'Personal Referral', '', 'New', '3.2', '2003-07-01');
INSERT INTO `registration` VALUES ('Scientist', '', 'Monash University', 'Australia', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-01');
INSERT INTO `registration` VALUES ('Graphics Professional', '', 'Gruzen Samton', 'United States', 'NY', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-02');
INSERT INTO `registration` VALUES ('Resource Manager', '', 'lukoil', 'Russian Federation', 'Select State', 'Choose One', 'Choose One', 'Search Engine', '', 'New', '3.2', '2003-03-07');
INSERT INTO `registration` VALUES ('Scientist', '', 'Kimley-Horn and Associates, Inc.', 'United States', 'CO', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-09');
INSERT INTO `registration` VALUES ('Scientist', '', 'Army Corps of Engineers', 'United States', 'WA', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-09');
INSERT INTO `registration` VALUES ('Scientist', '', 'Monash University', 'Australia', '', '', '', 'Search Engine', '', 'New', '3.2', '2003-07-09');
INSERT INTO `registration` VALUES ('Graphics Professional', '', 'consultant to Oberlin College', 'United States', 'OH', '', '10', 'Personal Referral', '', 'New', '3.2', '2003-07-10');
INSERT INTO `registration` VALUES ('Student (Graduate)', '', 'AOL', 'United States', 'CA', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-10');
INSERT INTO `registration` VALUES ('University Professor', '', 'University of Florida', 'United States', 'FL', '', '', 'Search Engine', '', 'New', '3.2', '2003-07-11');
INSERT INTO `registration` VALUES ('Scientist', '', 'CSIRO', 'Australia', '', 'WINDOWS', '10', 'Search Engine', '', 'New', '3.2', '2003-07-13');
INSERT INTO `registration` VALUES ('Student (Graduate)', '', '', 'Germany', '', '', '', 'Search Engine', '', 'New', '3.2', '2003-07-17');
INSERT INTO `registration` VALUES ('Scientist', '', 'University of South Florida, College of Marine Science', 'United States', 'FL', '', '10', 'Personal Referral', '', 'New', '3.2', '2003-07-17');
INSERT INTO `registration` VALUES ('Federal Agency', '', 'Great Barrier reef Marine Park Authority', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '', 'New', '3.2', '2003-07-17');
INSERT INTO `registration` VALUES ('Student (Graduate)', '', 'FACTIUM', 'Netherlands', '', '', '', 'Search Engine', '', 'New', '3.2', '2003-07-19');
INSERT INTO `registration` VALUES ('Local Agency', 'personal cover for home dvd', '', 'Italy', '', '', '', 'Search Engine', '', 'New', '3.2', '2003-07-21');
INSERT INTO `registration` VALUES ('Scientist', '', 'Queensland EPA', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '', 'New', '3.2', '2003-07-21');
INSERT INTO `registration` VALUES ('Student (Graduate)', '', 'Internet Ltd.', 'Hungary', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-22');
INSERT INTO `registration` VALUES ('Scientist', '', '', 'Italy', '', '', '', 'Search Engine', '', 'New', '3.2', '2003-07-24');
INSERT INTO `registration` VALUES ('Interested Public', '', 'CGNR', 'Russian Federation', 'Select State', 'Choose One', '10', 'Search Engine', '', 'New', '3.2', '2003-07-25');
INSERT INTO `registration` VALUES ('Interested Public', '', '', 'United Kingdom', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-27');
INSERT INTO `registration` VALUES ('Interested Public', '', '', 'United States', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-27');
INSERT INTO `registration` VALUES ('Interested Public', '', 'Centre for Riverine Landscapes', 'Australia', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-07-28');
INSERT INTO `registration` VALUES ('Scientist', '', 'UMCES HPL', 'United States', 'MD', 'WINDOWS', '10', 'Email from IAN', '', 'New', '3.2', '2003-07-30');
INSERT INTO `registration` VALUES ('Student (Graduate)', '', 'UMCES HPL', 'United States', 'MD', 'WINDOWS', '10', 'Email from IAN', '', 'New', '3.2', '2003-08-10');
INSERT INTO `registration` VALUES ('Graphics Professional', '', 'SUMMEDIA INC', 'Canada', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-08-12');
INSERT INTO `registration` VALUES ('Interested Public', '', '', 'Micronesia', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-08-12');
INSERT INTO `registration` VALUES ('Interested Public', '', '', 'Australia', '', '', '10', 'Search Engine', '', 'New', '3.2', '2003-08-12');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '--List Other Here--', 'widya kartika', 'Indonesia', '', 'WINDOWS', '10', 'Adobe Studio Exchange', '', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Graphics Professional', '', 'Self', 'United States', 'CA', 'WINDOWS', 'CS', 'Adobe User Forums', '--List Other Here--', 'Upgrade', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', '', 'United States', '', 'WINDOWS', '10', 'Search Engine', '', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'Latvia', '', 'WINDOWS', 'CS', 'Adobe User Forums', '--List Other Here--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', '--Enter Company Name--', 'Australia', '', 'WINDOWS', '10', 'Avenza MAPublisher', '--List Other Here--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'Australia', '', 'WINDOWS', '10', 'Adobe Studio Exchange', '--List rnrnOther Here--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'United States', 'RI', 'WINDOWS', 'CS', 'Adobe User Forums', '--List Other rnrnHere--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Student (Undergraduate)', '', '', 'Israel', '', 'MAC', '10', 'Adobe Studio Exchange', '--List Other Here--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Student (Graduate)', '--List Other Here--', '--Enter Company Name--', 'United States', 'WA', 'MAC', '10', 'Search Engine', '--List Other Here--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Graphics Professional', '--List Other Here--', 'The Creative Computer', 'United States', 'IL', 'MAC', '10', 'Adobe Studio Exchange', '--List rnrnOther Here--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'Canada', '', 'MAC', '10', 'Search Engine', '--List Other Here--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Interested Public', '', '--Enter Company Name--', 'Switzerland', '', 'MAC', 'CS', 'Adobe Studio Exchange', '--List Other Here--', 'New', '4.1', '2004-04-11');
INSERT INTO `registration` VALUES ('Teacher (9-12)', '', 'Gloriavale Christian Community School', 'New Zealand', '', 'MAC', '10', 'Adobe User Forums', '--List Other Here--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Resource Manager', '', 'RBX', 'Vietnam', '', 'WINDOWS', 'CS', 'Browsing IAN Website', '', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'Bosnia Herzegovina', '', 'WINDOWS', 'CS', 'Browsing IAN Website', '--List rnrnOther Here--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Interested Public', '', '--Enter Company Name--', 'United States', 'CA', 'WINDOWS', '10', 'Search Engine', '--List Other Here--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Resource Manager', '', 'New York Sea Grant', 'United States', 'NY', 'WINDOWS', 'CS', 'Personal Referral', '--List Other Here--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '', 'Thailand', '', 'WINDOWS', '10', 'Other Web Forum', 'www.vectorwork.com', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Graphics Professional', '', 'Xds', 'Russian Federation', 'Select State', 'WINDOWS', '10', 'Adobe User Forums', '--List Other Here--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Graphics Professional', '--List Other Here--', 'IC Holding A.G.', 'Russian Federation', 'Select State', 'WINDOWS', 'CS', 'Adobe Studio Exchange', '--List Other Here--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Graphics Professional', '--List Other Here--', 'The Wistar Institute', 'United States', 'PA', 'WINDOWS', '10', 'Adobe User Forums', '--List Other Here--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Graphics Professional', '--List Other Here--', 'blue designs', 'United States', 'AL', 'WINDOWS', 'CS', 'Email from IAN', '--List Other rnrnHere--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Interested Public', '--List Other Here--', '--Enter Company Name--', 'Albania', '', 'WINDOWS', 'CS', 'Adobe Studio Exchange', '--List Other rnrnHere--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('Scientist', '--List Other Here--', 'Qld Dept. Natural Resources Mines and Energy', 'Australia', '', 'WINDOWS', '10', '', '--List Other Here--', 'New', '4.1', '2004-04-12');
INSERT INTO `registration` VALUES ('State Agency', 'Catchment Modeller', 'NRM&E', 'Australia', '', 'WINDOWS', '10', 'Personal Referral', '', 'New', '4.1', '2004-04-12');
|
|
#6
April 13th, 2004, 12:32 AM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
OK, I am slowly getting somewhere. First of all - some new registrations have upped the numbers to 32 countries, 30 of which show up with a COUNT DISTINCT.
If I run this query ('SELECT DISTINCT country FROM registration' - see code below) only the 30 countries show up. The two that are missing are the 'United States' and 'Vietnam'. These both show up when I do a: SELECT country, COUNT( * ) AS cat_num FROM registration GROUP BY country ORDER BY cat_num DESC, country ASC Some more trial and error testing shows that 'Ivory Coast' is also excluded in a COUNT DISTINCT Hope this helps a little more! Code:
SQL-query: SELECT DISTINCT country FROM registration Rows: 30 country Albania Australia Austria Azerbaijan Bosnia Herzegovina Brazil Canada Colombia Finland Germany Hungary India Indonesia Israel Italy Japan Latvia Mexico Micronesia Netherlands New Caledonia New Zealand Panama Philippines Portugal Russian Federation Switzerland Taiwan Thailand United Kingdom
|
|
#7
April 13th, 2004, 12:47 AM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
OOPS Sorry about that. I am using PHPMyAdmin and by default it only displays 30 rows at a time. So to clarify - and this is even more confusing!
Code:
SELECT COUNT( DISTINCT country ) FROM registration yields '30' BUT the following lists all 32 countries and they both make use of DISTINCT!! Code:
SELECT DISTINCT country FROM registration Albania Australia Austria Azerbaijan Bosnia Herzegovina Brazil Canada Colombia Finland Germany Hungary India Indonesia Israel Italy Japan Latvia Mexico Micronesia Netherlands New Caledonia New Zealand Panama Philippines Portugal Russian Federation Switzerland Taiwan Thailand United Kingdom United States Vietnam
|
|
#8
April 13th, 2004, 03:10 PM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
OK Here is something simpler for anyone to try.
I took out all the other fields - now only the country field and I still only get a DISTINCT COUNT of 30, but there are definitely 32 countries listed and in this test, only one instance of each - HELP Please!! Code:
CREATE TABLE `test` (
`country` enum('Select Country','Afghanistan','Albania','Algeria','Americ an Virgin Islands','Andorra','Angola','Anguilla','Antarctica ','Antigua Barbados','Argentina','Armenia','Aruba','Australia ','Austria','Azerbaijan','Bahamas','Bahrain','Bang ladesh','Barbados','Belarus','Belgium','Belize','B enin','Bermuda','Bhutan','Bolivia','Bosnia Herzegovina','Botswana','Bouvet Island','Brazil','British Indian Ocean Territories','British Virgin Islands','Brunei Dar es S','Bulgaria','Burkina Faso','Burundi','Cambodia','Cameroon','Canada','Ca pe Verde','Cayman Islands','Central African Republic','Chad','Channel Islands','Chile','China','Christmas Islnd','Coconut Islands','Colombia','Comoro','Congo','Cook Islands','Costa Rica','Croatia','Cuba','Cyprus','Czech Republic','Denmark','Djibouti','Dominica','Dominic an Republic','Ecuador','Egypt','El Salvador','Equatorial Guin','Eritrea','Estonia','Ethiopia','Faeroe Islands','Falkland Islnds','Fiji','Finland','France','French Polynesia','French Guiana','Gabon','Gambia','Georgia','Germany','Ghan a','Gibraltar','Greece','Greenland','Grenada','Gua deloupe','Guam','Guatemala','Guinea','Guinea Bissau','Guyana','Haiti','Heard McDonald Island','Honduras','Hong Kong','Hungary','Iceland','India','Indonesia','Ira n','Iraq','Ireland','Israel','Italy','Ivory Coast','Jamaica','Japan','Jordan','Kazakhstan','Ke nya','Kirghistan','Kiribati','Kuwait','Laos','Latv ia','Lebanon','Lesotho','Liberia','Libya','Liechte nstein','Lithuania','Luxembourg','Macau','Macedoni a','Madagascar','Malawi','Malaysia','Maldives','Ma li','Malta','Marshall Islnds','Martinique','Mauritania','Mauritius','May otte','Mexico','Micronesia','Minor Outl Islands','Moldavia','Monaco','Mongolia','Montserra t','Morocco','Mozambique','Myanmar','N Mariana Islnd','Namibia','Nauru','Nepal','Neth Antilles','Netherlands','New Caledonia','New Zealand','Nicaragua','Niger','Nigeria','Niue Islands','Norfolk Island','North Korea','Norway','Oman','Pakistan','Palau','Panama' ,'Papua New Guinea','Paraguay','Peru','Philippines','Pitcairn Islnds','Poland','Portugal','Puerto Rico','Qatar','Reunion','Romania','Russian Federation','Rwanda','S Tome Principe','Samoa American','San Marino','Saudi Arabia','Senegal','Seychelles','Sierra Leone','Singapore','Slovakia','Slovenia','Solomon Islands','Somalia','South Africa','South Korea','Spain','Sri Lanka','St Helena','St Lucia','St Vincent','St Kitts Nevis','St Pier Miquel','Sudan','Suriname','Svalbard','Swaziland', 'Sweden','Switzerland','Syria','Tadzhikistan','Tai wan','Tanzania','Thailand','Togo','Tokelau Islands','Tonga','Trinidad Tobago','Tunisia','Turkey','Turkmenistan','Turks and Caicos','Tuvalu','Uganda','Ukraine','United Kingdom','United States','Uruguay','United Arab Emirates','Uzbekistan','Vanuatu','Vatican City','Venezuela','Vietnam','Wallis Futuna','West Sahara','Western Samoa','Yemen','Yugoslavia','Zaire','Zambia','Zimb abwe') NOT NULL default 'Select Country'
) TYPE=MyISAM;
INSERT INTO `test` VALUES ('Albania');
INSERT INTO `test` VALUES ('Australia');
INSERT INTO `test` VALUES ('Austria');
INSERT INTO `test` VALUES ('Azerbaijan');
INSERT INTO `test` VALUES ('Bosnia Herzegovina');
INSERT INTO `test` VALUES ('Brazil');
INSERT INTO `test` VALUES ('Canada');
INSERT INTO `test` VALUES ('Colombia');
INSERT INTO `test` VALUES ('Finland');
INSERT INTO `test` VALUES ('Germany');
INSERT INTO `test` VALUES ('Hungary');
INSERT INTO `test` VALUES ('India');
INSERT INTO `test` VALUES ('Indonesia');
INSERT INTO `test` VALUES ('Israel');
INSERT INTO `test` VALUES ('Italy');
INSERT INTO `test` VALUES ('Japan');
INSERT INTO `test` VALUES ('Latvia');
INSERT INTO `test` VALUES ('Mexico');
INSERT INTO `test` VALUES ('Micronesia');
INSERT INTO `test` VALUES ('Netherlands');
INSERT INTO `test` VALUES ('New Caledonia');
INSERT INTO `test` VALUES ('New Zealand');
INSERT INTO `test` VALUES ('Panama');
INSERT INTO `test` VALUES ('Philippines');
INSERT INTO `test` VALUES ('Portugal');
INSERT INTO `test` VALUES ('Russian Federation');
INSERT INTO `test` VALUES ('Switzerland');
INSERT INTO `test` VALUES ('Taiwan');
INSERT INTO `test` VALUES ('Thailand');
INSERT INTO `test` VALUES ('United Kingdom');
INSERT INTO `test` VALUES ('United States');
INSERT INTO `test` VALUES ('Vietnam');
|
|
#9
April 13th, 2004, 03:14 PM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
OK Sorry to keep posting like this, but just discovered that if I change the country field from ENUM to VARCHAR it counts correctly - anyone have any ideas why this might be?
|
|
#10
April 13th, 2004, 06:46 PM
|
|||
|
|||
|
RE: COUNT DISTINCT inaccurate
I am not exactly sure why, but notice in the list of countries,
you have two countries that start with "NEW" and two countries that start with "UNITED" it seems that the distinct statement is interpreting those entries as the same based on the first word in the country name. I will investgigate further!!!
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
