How to get count of Primary key by multiple columns and their categories in Vertica SQL?

Question

I have table as given below. (3 columns and 10 rows)

ID cat_1 cat_2
1001    High    High
1002    Mid High
1003    Mid High
1004    <null>  <null>
1005    <null>  Low
1006    High    High
1007    <null>  <null>
1008    High    Mid
1009    Low Low
1010    High    High

And I would want to calculate the count of ID for each of the columns. I don't want to run multiple queries. Is there a simple way to achieve this?

Category    cat_1   cat_2
High    4   5
Mid 2   1
Low 1   2
<null>  3   2

Currently, I only know to run multiple queries of group by statements ("select cat_1, count(ID) from table group by 1"). I know this approach is not the correct one. Thanks!

Answer 1

You can unpivot and aggregate. Here is a general approach:

select cat, sum(cat_1), sum(cat_2)
from ((select cat_1 as cat, 1 as cat_1, 0 as cat_2
       from t
      ) union all
      (select cat_2, 0 as cat_1, 1 as cat_2
       from t
      )
     ) c
group by cat;

Here is a db<>fiddle.