Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Cool Unanswered: Problem with complex query

    Hello!

    I have a query that queries two tables (including a self join on one of them) and returns a result set that almost (but not quite) gives me what I want , and was wondering if someone could give me some pointers. Rather than show the whole query (complex), I'll show the result set and describe what i want:

    Tab1.efID Tab1.VID Tab2.efID Tab2.VID
    $00046342 7 $00046342 8
    $00046342 7 $00046342 19
    $00046342 18 $00046342 19

    I want to amend the query so that it returns a count of the distinct rows of Tab1.efID,Tab1.VID - from the above result set, it should return just a count of the first and third rows, i.e 2

    The statement SELECT DISTINCT Tab1.efID,Tab1.VID would return the two rows, but obviously SELECT COUNT(DISTINCT Tab1.efID,Tab1.VID) doesn't work.

    SELECT COUNT(DISTINCT Tab1.efID + CAST(Tab1.VID AS VARCHAR(2))) does work, but i thought perhaps there may be a more elegant solution - anyone have any pointers?

    Cheers
    Greg

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    GROUP BY

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Pat: I think he goes for a count of unique rows instead of the unique rows alone.

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    Also not so elegant:
    select distinct Col1, Col2.....
    select @@rowcount

    perhaps it's possbile to add a computed column that merges Col1 and Col2 as you did, making the count distinct on a single column possible.

    EDIT: or:
    select count(*)
    from (select distinct Col1, Col2) t1

    But I can't think of a really neat solution though.
    Last edited by Kaiowas; 08-20-04 at 13:32.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Kaiowas
    Pat: I think he goes for a count of unique rows instead of the unique rows alone.
    You could use Count(*) with GROUP BY, or am I missing something? Even if he just wanted a count of the number of groups, he could create a virtual table using GROUP BY and then count the number of rows in that table, something like:
    Code:
    SELECT Count(*)
       FROM (SELECT a.Efid, a.VID
          FROM myTable AS a
          GROUP BY a.Efid, a.VID) AS b
    -PatP

  6. #6
    Join Date
    Feb 2004
    Posts
    32
    Thanks guys,
    I'll try them out.
    Greg

Posting Permissions

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