CODEWITHSUNDEEP
sqlgoogle-bigquery
Chris
Chris

Reputation: 2071

Counting multiple columns distinct values grouped

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?

Upvotes: 0

Views: 197

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173013

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

enter image description here

Upvotes: 3

Related Questions