How to Count(Distinct ID) where the Value is equal 1; possibly using
ROW_NUMBER() OVER(PARTITION BY ... ) ?
-
Why should I not upload images of code/data/errors?SelVazi– SelVazi2024-01-19 07:34:38 +00:00Commented Jan 19, 2024 at 7:34
Add a comment
|
1 Answer
From the data you have supplied, we are able to deduce that you need to determine the count of distinct ID for each GROUP and YRMO where VALUE = 1 and designate it as a distinct count.
The COUNT(CASE WHEN condition THEN column END) syntax allows us to calculate the number of rows that match a condition :
SELECT GRP, YRMO, COUNT(DISTINCT CASE WHEN val = 1 THEN id END) AS distinct_counts,
COUNT(DISTINCT id) AS total_counts
FROM mytable
GROUP BY GRP, YRMO
