MySQL extending SELECT query with COUNT

Question

I need some help in creating a MySQL query. Let's assume we have a table "animals" containing the species and also the detailed breed of animals. Furthermore we have a table "examinations" containing investigations on animals. To find out how many animals there are of certain breeds that were examined (we're not interested in finding out how many examinations occured on certain breeds!), we could run this query:

SELECT animals.animal_species, 
       animals.animal_breed, 
       Count(DISTINCT animals.animal_id) 
FROM   examinations, 
       animals 
WHERE  examinations.examination_animal_id = animals.animal_id 
       AND animals.animal_breed IS NOT NULL 
GROUP  BY animals.animal_species, 
          animals.animal_breed 
ORDER  BY animals.animal_species

By running this we get something like:

dog | sheepdog    | 3
dog | collie      | 1
dog | terrier     | 5
cat | Persian cat | 3
cat | Birman cat  | 2

Now I want to include a total sum of each species. The result should look like this:

dog | sheepdog    | 3 | 9
dog | collie      | 1 | 9
dog | terrier     | 5 | 9
cat | Persian cat | 3 | 5
cat | Birman cat  | 2 | 5

Can you please tell me how I have to change my query to achieve this? I tried several solutions, but none of them worked ...

Thank you so very much in advance!

Provide sum of what exactly?

Eyeslandic
– Eyeslandic

2017-05-28 11:09:54 +00:00
Commented May 28, 2017 at 11:09 — Eyeslandic
– Eyeslandic, Commented May 28, 2017 at 11:09

Steve Lovell · Accepted Answer · 2017-05-28 11:15:32Z

1

I think the following will meet your needs, though it's entirely possible that more efficient solutions are available. This adds a subquery to your code which gets the totals for each species and then adds that total to the select. I've also replaced your "old style" JOINs with the more modern and preferred equivalent:

SELECT
    a.animal_species,
    a.animal_breed,
    COUNT(DISTINCT a.animal_id) as animals_examined,
    species_count.species_animals_examined
    FROM examinations e
JOIN animals a ON
    e.examination_animal_id = a.animal_id
JOIN 
    (SELECT
         a2.animal_species,
         count(distinct a2.animal_id) as species_animals_examined
    FROM examinations e2
    JOIN animals a2 ON
        e2.examination_animal_id = a2.animal_id
    WHERE
        a2.animal_breed IS NOT NULL
    GROUP BY a2.animal_species
    ) as species_count ON
    species_count.animal_species = a.animal_species
WHERE
    a.animal_breed IS NOT NULL
GROUP BY a.animal_species, a.animal_breed
ORDER BY a.animal_species

answered May 28, 2017 at 11:15

Steve Lovell

2,5742 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

Wow, this was a really fast reply and it works absolutely perfect! Thank you so much and have a great day!

Collectives™ on Stack Overflow

MySQL extending SELECT query with COUNT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related