DB2 Distinct Count

Question

How to Count(Distinct ID) where the Value is equal 1; possibly using ROW_NUMBER() OVER(PARTITION BY ... ) ?

Answer 1

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