CODEWITHSUNDEEP
sqloracle11ganalytics
zundarz
zundarz

Reputation: 1594

How to use analytic count to conditionally count?

Using this example, how can I tweak my sql to report whether a listing_id has passed all the tests?

with listing_row 
as
(
select 1 as listing_id, 'TEST1' as listing_test, 'Y' as pass_yn from dual union all  
select 1 as listing_id, 'TEST2' as listing_test, 'Y' as pass_yn from dual union all
select 1 as listing_id, 'TEST3' as listing_test, 'Y' as pass_yn from dual union all

select 2 as listing_id, 'TEST1' as listing_test, 'N' as pass_yn from dual union all  
select 2 as listing_id, 'TEST2' as listing_test, 'Y' as pass_yn from dual union all
select 2 as listing_id, 'TEST3' as listing_test, 'N' as pass_yn from dual union all

select 3 as listing_id, 'TEST1' as listing_test, 'N' as pass_yn from dual union all  
select 3 as listing_id, 'TEST2' as listing_test, 'N' as pass_yn from dual union all
select 3 as listing_id, 'TEST3' as listing_test, 'N' as pass_yn from dual)
select listing_id, 
       listing_test,pass_yn, 
       count(*) over (partition by listing_id, pass_yn) as all_y,
       count(*) over (partition by listing_id, pass_yn) as all_n
         from listing_row

Desired Results

LISTING_ID   ALL_Y ALL_N
1            Y       N
2            N       N
3            N       Y

Upvotes: 1

Views: 777

Answers (2)

kasiom
kasiom

Reputation: 11

This is another solution using sum():

SELECT listing_id,
       CASE WHEN max(all_test_cnt)-MAX(num_of_test) = 0 THEN 'Y' ELSE 'N' END all_y,
       CASE WHEN MAX(num_of_test) = 0 THEN 'Y' ELSE 'N' END all_n
FROM
    (SELECT listing_id,
           COUNT(DISTINCT listing_test) OVER (PARTITION BY NULL) all_test_cnt,
           SUM(CASE WHEN pass_yn = 'Y' THEN 1 ELSE 0 END) OVER (PARTITION BY listing_id)num_of_test
    FROM listing_row)

GROUP BY listing_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I think the easiest way is to use min() and max():

select listing_id, 
       listing_test, pass_yn, 
       min(pass_yn) over (partition by listing_id) as all_y,
       min(case when pass_yn = 'Y' then 'N' else 'Y' end) over (partition by listing_id) as all_n
from listing_row;

This uses a trick based on the fact the "Y" > "N". So, if you take the min() of the column and it has any "N" values, then the result will be "N".

Upvotes: 1

Related Questions