Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Counting Problem

    Let's say I have the following two tables:
    Code:
    Table 1                 Table 2
    ID  COL1    COL2        ID  COL1    COL2
    1   1000    A           1   1002    A
    2   1001    B           2   1000    A
    3   1000    A           3   1001    B
    I want to be able to count the number of unqiue occurances of COL1 in both tables where COL2 = 'A'. The solution I came up with is as follows:

    Code:
    SELECT DISTINCT COL1 FROM Table1 WHERE COL2='A' GROUP BY COL1
    UNION SELECT DISTINCT COL1 FROM Table2 WHERE COL2='A' GROUP BY COL1
    I then simply look at the rs.RecordCount to give me the number of unique COL1 values accros both tables. Is there a way to actually get the query itself to return the value itself, rather than having to use RecordCount? And, which method would be considered best? Mine, or having the query return the result?

    I tried this way:
    Code:
    SELECT DISTINCT Count(COL1) AS Cnt
    FROM Table1
    WHERE (COL2='A');
    But it gives only a count of each COL1 item, rather than each unique item.
    Last edited by bcass; 05-11-07 at 10:01.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you ONLY want the count or do you want the actual values too? If just the count then a query.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, this
    Code:
    SELECT DISTINCT COL1 FROM Table1 WHERE COL2='A' GROUP BY COL1
    UNION SELECT DISTINCT COL1 FROM Table2 WHERE COL2='A' GROUP BY COL1

    can be shortened to this, which is likely to be more efficient
    Code:
     
    SELECT COL1 FROM Table1 WHERE COL2='A' UNION SELECT COL1 FROM Table2 WHERE COL2='A'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I just want the count. So, given the example tables above, a count of unique COL1s where COL2 = A would give a count of 2.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bcass
    I just want the count. So, given the example tables above, a count of unique COL1s where COL2 = A would give a count of 2.
    Not 3?????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Anyhoo:

    Code:
     
    SELECT COUNT (*) AS no_of_distinct_values
    FROM (SELECT COL1 FROM Table1 WHERE COL2='A'
     UNION SELECT COL1 FROM Table2 WHERE COL2='A') AS dist_cols
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Quote Originally Posted by pootle flump
    Not 3?????
    No. Where COL2 = A, the total unique COL1 values is 2 (1000 and 1002).

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Quote Originally Posted by pootle flump
    Anyhoo:

    Code:
     
    SELECT COUNT (*) AS no_of_distinct_values
    FROM (SELECT COL1 FROM Table1 WHERE COL2='A'
     UNION SELECT COL1 FROM Table2 WHERE COL2='A') AS dist_cols
    Thanks for that. It seems obvious now that I see it. At least I was almost there with my original idea!

    So, which method would be considered most optimal? Yours, which returns the value, or mine, where you could get the value from a RecordCount?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bcass
    No. Where COL2 = A, the total unique COL1 values is 2 (1000 and 1002).
    Don't forget 1001
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mine. ESPECIALLY if you are using a client\server set up
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Quote Originally Posted by pootle flump
    Don't forget 1001
    1001 Has COL2 = B, not A!

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bcass
    1001 Has COL2 = B, not A!
    .
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    No matter! Thanks a lot for your help. Much appreciated.

Posting Permissions

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