Can I get an output like the following from my pet database wherein I would like to group Animal Types into either Household Pets or Exotic Pets and get a total for each group. Column headings for my table are: AnimalID, AnimalName, AnimalType, AnimalDOB, AnimalVisitDate and ClientID. The Output will look like this:
PetType NumberofAnimals Household Pets 50 Exotic Pets 15
I know the COUNT(*) for aggregates but I could not get 'Household Pets' and 'Exotic Pets' on two rows under a new column heading. Could you please show me? Thanks!
If you had a PetType table, to group your AnimalType values into Pet Types, you would simply join it to the Pets table, and you'd be in business.
PetTypes PetType AnimalType Dog Household Pets Cat Household Pets Snake Exotic Pets Tarantula Exotic Pets
If you do not have such a table, you can still do the query, but it's messy and difficult to maintain. You'll be constantly revising it as you discover new animal types, instead of just logging them into the PetTypes table, and then, to enter a new pet, just choosing values from a dropdown list.
So here's the messy query:
select case when AnimalType in ( 'Dog', 'Cat' ) then 'Household Pets' when AnimalType in ( 'Snake', 'Tarantula' ) then 'Exotic Pets' else 'Unknown' end as PetType , count(*) as NumberofAnimals from Pets group by case when AnimalType in ( 'Dog', 'Cat' ) then 'Household Pets' when AnimalType in ( 'Snake', 'Tarantula' ) then 'Exotic Pets' else 'Unknown' end
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2004