AlmostThere AlmostThere - 3 months ago 11
SQL Question

Creating categories based on rows in SQL

I have the following table called "Animal".

Animal Drink
Dog Water
Dog Water
Dog Water
Cat Milk
Cat Milk
Cat Water


I am trying to create categories based on the type of drink the animal is drinking and the result would be.

Animal Drink
Dog Water
Cat Mixed


I have tried various case statements but have been unsuccessful. The issue is that I am comparing rows (instead of columns), which has proven to be quite difficult for me.

Essentially, I want to single out animals that only drink water.
If an animal only drinks water than it would be categorized as "Water". If an animal drinks water and anything else, than it would be categorized as "Mixed". If the animal does not drink any water, then it would categorized "NonWater"

Answer

For that you can simply use a group by expression :

SELECT
  animal,
  CASE
    WHEN (sum_other = 0) THEN 'Water'
    WHEN (sum_water = 0) THEN 'NonWater'
    ELSE 'Mixed'
  END
FROM (SELECT
  animal,
  SUM(CASE
    WHEN (drink = 'Water') THEN 1
    ELSE 0
  END) AS sum_water,
  SUM(CASE
    WHEN (drink <> 'Water') THEN 1
    ELSE 0
  END) AS sum_other
FROM my_table
GROUP BY animal) A
Comments