CODEWITHSUNDEEP
mysqlselectcountsubquery
Ben
Ben

Reputation: 25

MySQL extending SELECT query with COUNT

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!

Upvotes: 1

Views: 41

Answers (1)

Steve Lovell
Steve Lovell

Reputation: 2564

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

Upvotes: 1

Related Questions