[Programmers / MySQL] Level 2 How Many Cats and Dogs Are There (59040)
[Programmers / MySQL] Level 2 How Many Cats and Dogs Are There (59040)
| Rank | Language Used |
|---|---|
| Level 1 | 🖼️ MySQL |
🔗 How Many Cats and Dogs Are There
The ANIMAL_INS table holds information about animals that came into the animal shelter. The ANIMAL_INS table structure is as follows, where ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, and SEX_UPON_INTAKE respectively represent the animal's ID, species, intake date, condition at intake, name, and sex/neuter status.
| NAME | TYPE | NULLABLE |
|---|---|---|
| ANIMAL_ID | VARCHAR(N) | FALSE |
| ANIMAL_TYPE | VARCHAR(N) | FALSE |
| DATETIME | DATETIME | FALSE |
| INTAKE_CONDITION | VARCHAR(N) | FALSE |
| NAME | VARCHAR(N) | TRUE |
| SEX_UPON_INTAKE | VARCHAR(N) | FALSE |
Write a SQL statement that finds how many cats and how many dogs came into the animal shelter. Retrieve cats before dogs.
For example, if the ANIMAL_INS table is as follows
| ANIMAL_ID | ANIMAL_TYPE | DATETIME | INTAKE_CONDITION | NAME | SEX_UPON_INTAKE |
|---|---|---|---|---|---|
| A373219 | Cat | 2014-07-29 11:43:00 | Normal | Ella | Spayed Female |
| A377750 | Dog | 2017-10-25 17:17:00 | Normal | Lucy | Spayed Female |
| A354540 | Cat | 2014-12-11 11:48:00 | Normal | Tux | Neutered Male |
There are 2 cats and 1 dog. So running the SQL statement should produce the following.
Therefore, running the SQL statement should produce the following.
| ANIMAL_TYPE | count |
|---|---|
| Cat | 2 |
| Dog | 1 |
- Retrieve animals whose ANIMAL_TYPE is Cat or Dog.
- Retrieve ANIMAL_TYPE and the count.
- Retrieve them ordered alphabetically by ANIMAL_TYPE.
Using GROUP BY, we can group by ANIMAL_TYPE to get the counts of Cat and Dog. Use ORDER BY to sort alphabetically by ANIMAL_TYPE.
SQL
SELECT ANIMAL_TYPE, COUNT(*) FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE;