Results 1 to 2 of 2

Thread: Freaky Count

  1. #1
    Join Date
    Mar 2004

    Arrow Unanswered: Freaky Count

    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'
    ( SELECT COUNT(DISTINCT col2) sum_col2
    FROM table_1
    WHERE col3 = 'N'
    ( 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

    Can you suggest something simpler.


  2. #2
    Join Date
    Sep 2004
    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
    (select sum(decode(col3, 'n', count(distinct col2),
    'y', count(distinct col1)
    from vincount
    group by col3

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

    This query involves only two selects, and uses group functions
    Last edited by vinitasinha; 10-11-04 at 02: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