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
>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
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.