SQL/Postgres group by and count with fallback?-CodePudding

Let's say I have a table things with 3 columns, id, animal and color. For each row animal always has a value and color sometimes has a value. I need to count how many of each color, and for those rows without color, how many of each animal. Is this possible with one query? Would look like:

  things  
   id  | animal  |  color  |
    1     dog         black
    2     cat         black
    3     dog          null
    4     cat          null

I would want to get back essentially: black: 2, cat: 1 and dog: 1

CodePudding user response：

You can do this with a UNION.

I would advise adding a literal column which specifies which result set it is. This way, you can see what came from where.

You can just delete the ThingType columns if you don't need them.

SELECT 
   -- My addition - delete if not wanted
   'ByColor' as ThingType,
   color as ThingCounted,
   COUNT(DISTINCT animal) AS ThingCount
FROM
   things
GROUP BY
  color
UNION
SELECT 
   -- My addition - delete if not wanted
   'ByAnimal' as ThingType,
   animal as ThingCounted,
   COUNT(DISTINCT animal) AS ThingCount
FROM
   things
GROUP BY
   animal