Counting multiple columns distinct values grouped

Question

I have been trying to get this done, but I don't see how I can do this with CASE WHEN. Specifically, I'm grouping by id and I need to count every distinct value for each column (I'm not looking for this or this, even if it seems to be the same question). For example, I have this data:

ID Var_A Var_B
1  A      2022-01
1  B      2022-01
1  C      2022-01
2  A      2022-01
2  A      2022-02
2  Z      2022-03
2  Z      2022-01

Expected output

ID N_Var_A Var_B
1  3       1
2  2       4

You can see that I'm looking for the number of distinct values in each column for each id. I have been trying with:

SELECT ID, COUNT(Var_A), COUNT(Var_B)
FROM TABLE
GROUP BY ID

Without success. Any suggestions?

Answer 1

use below instead

select ID, 
  count(distinct Var_A) N_Var_A,
  count(distinct Var_B) N_Var_B
from your_table
group by ID    

if applied to sample data in your question - output is