Results 1 to 2 of 2

Thread: Freaky Count

  1. #1
    Join Date
    Mar 2004
    Posts
    50

    Arrow Unanswered: Freaky Count

    Hi,
    There is a table like following :

    table_1 :

    col1 col2 col3
    a u Y
    b v Y
    c w N
    d u N
    a b N

    i want count (distinct col1 if col3='Y') + count (distinct col2 if col3='N') - count (distinct col1 if for a particular value of col1, col3='Y' as well as col3 = 'N')

    The query tht i m using is :

    select sum_col1 + sum_col2 - common from
    ( SELECT COUNT(DISTINCT col1) sum_col1
    FROM table_1
    WHERE col3 = 'Y'
    )a,
    ( SELECT COUNT(DISTINCT col2) sum_col2
    FROM table_1
    WHERE col3 = 'N'
    )b,
    ( SELECT COUNT(DISTINCT col1) common
    FROM table_1 c1, table_1 c2
    WHERE c1.col3 = 'Y'
    and c2.col3='N'
    and c1.col1=c2.col1
    )c

    Can you suggest something simpler.

    Thanks,
    Ishan

  2. #2
    Join Date
    Sep 2004
    Posts
    16
    Hi,
    You could try this:

    SQL> select * from vincount;

    C C C
    - - -
    a u y
    b v y
    c w n
    d u n
    a b n
    b l y

    6 rows selected.
    --------------------------------------------------
    The query:
    select sum12.count12 - sum3.count3
    from
    (select sum(decode(col3, 'n', count(distinct col2),
    'y', count(distinct col1)
    )
    )count12
    from vincount
    group by col3
    )sum12,

    (select count(distinct col1) count3
    from vincount
    group by col1
    having count(distinct col3) > 1
    )sum3

    This query involves only two selects, and uses group functions
    --Vinita
    Last edited by vinitasinha; 10-11-04 at 03:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •