At 2:08 -0800 2/9/02, Joe Chesak wrote: >I am attempting to combine COUNT, GROUP BY, and LEFT >JOIN.. Adopting the theme on >http://www.mysql.com/doc/C/o/Counting_rows.html my >goal is to display only species/sex combinations that >occur more than once. Additionally I wish to include >the count for each row, and some keyed field from >another table..
When you do a GROUP BY, you can't just arbitrarily include some other stuff. See below. > >So I added a separate table "owner" containing foreign >key "petID" and the field "zipcode".. > >A couple rows of output might look like (species=dog >sex=male animalcount=3 zipcode=55555) (species=dog >sex=female animalcount=2 zipcode=55555). There would >be no rows with a count of 1, and zipcode can repeat.. > > >The following statement unfortunately includes zipcode >when determining uniqueness. It's as close as I've >come! Any suggestions?: > >SELECT species, sex, zip, COUNT(*) as animalcount FROM >pet LEFT JOIN owner ON pet.petID = owner.petID GROUP >By species, sex HAVING animalcount > 1; With GROUP BY, you can select only the values of the grouped columns and the values of the summary function (COUNT() in this case. If you include other columnes, you change the meaning of the query. You say that when zipcode is added, it's used in determining uniqueness. There's probably something else going on, but suppose that's what's really happening. And suppose that zip is not unique for a given species and sex. Which one should MySQL choose? Which row having a given species and sexid combination should MySQL use for picking a pet.petID value to association with the owner.petID so that it can pick a zipcode value? What's the solution? If I understand your first paragraph correctly, I *think* what you may be able to do is generate the species/sex counts into another table. Then join that with the original table and join the result to the owner table based on the petID values. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
